COALESCE Function
1 min readThe Oracle COALESCE() function accepts a list of arguments and returns the first one that evaluates to a non-null value.
The following illustrates the syntax of the Oracle COALESCE() function:
COALESCE(e1, e2, ..., en)
In this syntax, the COALESCE() function returns the first non-null expression in the list. It requires at least two expressions. In case all expressions evaluate to null, the function returns null.
The following example returns one because it is the first non-null argument:
SELECT
COALESCE(NULL,1) -- return 1
FROM
dual;
select SEGMENT_NAME,EXTENTS,sum(bytes/1024/1024/1024)"size in gb" from dba_segments where segment_name='PROD_TX_UPDATE_TRK' group by segment_name,extents;
no rows selected
SQL> show pdbs;
SQL> alter session set container=PROD;
select SEGMENT_NAME,EXTENTS,sum(bytes/1024/1024/1024)"size in gb" from dba_segments where segment_name='PROD_TX_UPDATE_TRK' group by segment_name,extents;
SEGMENT_NAME EXTENTS size in gb
------------- -------------------
PROD_TX_UPDATE_TRK 6
Select last_analyzed from dba_ind_statistics where index_name='PROD_TX_UPDATE_TRK COALESCE';
ALTER INDEX RR.PROD_TX_UPDATE_TRK COALESCE;
