SQL> create table test_segments as
2 select *
3 from dba_segments where 1=2
4 /
Table created.
SQL> insert into test_segments
2 select * from dba_segments
3 where bytes < 100000
4 and owner = 'SYS'
5 and rownum < 6
6 /
5 rows created.
SQL> insert into test_segments
2 select * from dba_segments
3 where bytes >= 100000
4 and bytes < 150000
5 and owner = 'SYS'
6 and rownum < 6
7 /
5 rows created.
SQL> insert into test_segments
2 select * from dba_segments
3 where bytes >= 150000
4 and owner = 'SYS'
5 and rownum < 6
6 /
5 rows created.
SQL>
A very simple CASE expression might have a couple of WHEN conditions. If none of these are satisfied, the output will be blank:
SQL> select segment_name,
2 case
3 when bytes < 100000 then 'SMALL'
4 when bytes < 150000 then 'MEDIUM'
5 end as object_size
6 from test_segments
7 order by 1
8 /
SEGMENT_NAME OBJECT_SIZE
------------------------------ -----------
C_USER#
I_CCOL1
I_CDEF3 SMALL
I_COBJ# MEDIUM
I_COL3
I_FILE1 SMALL
I_IND1 MEDIUM
I_OBJ1
I_OBJ2
I_TAB1 SMALL
I_TS# SMALL
I_USER# MEDIUM
I_USER2 MEDIUM
I_VIEW1 MEDIUM
UNDO$ SMALL
15 rows selected.
SQL>
To get round this, you can add an ELSE at the end, if you wish:
SQL> select segment_name,
2 case
3 when bytes < 100000 then 'SMALL'
4 when bytes < 150000 then 'MEDIUM'
5 else 'LARGE'
6 end as object_size
7 from test_segments
8 order by 1
9 /
SEGMENT_NAME OBJECT_SIZE
------------------------------ -----------
C_USER# LARGE
I_CCOL1 LARGE
I_CDEF3 SMALL
I_COBJ# MEDIUM
I_COL3 LARGE
I_FILE1 SMALL
I_IND1 MEDIUM
I_OBJ1 LARGE
I_OBJ2 LARGE
I_TAB1 SMALL
I_TS# SMALL
I_USER# MEDIUM
I_USER2 MEDIUM
I_VIEW1 MEDIUM
UNDO$ SMALL
15 rows selected.
SQL>
And you can even have nested CASE expressions. Also note how the CASE may, or may not, be followed immediately by a column name:
SQL> select segment_name,
2 case segment_type
3 when 'TABLE' then
4 case
5 when bytes < 100000 then 'SMALL TABLE'
6 when bytes < 150000 then 'MEDIUM TABLE'
7 else 'LARGE TABLE'
8 end
9 when 'INDEX' then
10 case
11 when bytes < 100000 then 'SMALL INDEX'
12 when bytes < 150000 then 'MEDIUM INDEX'
13 else 'LARGE INDEX'
14 end
15 end as size_and_type
16 from
17 test_segments
18 order by 1
19 /
SEGMENT_NAME SIZE_AND_TYPE
------------------------------ -------------
C_USER#
I_CCOL1 LARGE INDEX
I_CDEF3 SMALL INDEX
I_COBJ# MEDIUM INDEX
I_COL3 LARGE INDEX
I_FILE1 SMALL INDEX
I_IND1 MEDIUM INDEX
I_OBJ1 LARGE INDEX
I_OBJ2 LARGE INDEX
I_TAB1 SMALL INDEX
I_TS# SMALL INDEX
I_USER# MEDIUM INDEX
I_USER2 MEDIUM INDEX
I_VIEW1 MEDIUM INDEX
UNDO$ SMALL TABLE
15 rows selected.
SQL>
