Use of Oracle decode functions

The Oracle decode function, regardless of whether the result of the query satisfies ' PRIMARY ', is executed by the SELECT statement in the parameter, and you know by looking at the execution plan

Set Autotrace traceonly

select decode(database_role ,‘PRIMARY‘, (selecttrunc((size_MB-free_MB)*100/maxsize_MB) "retvalue"from (selecttablespace_name,case when sum(decode(autoextensible,‘YES‘,1,0))>0 then ‘YES‘else ‘NO‘ end ext,trunc(sum(bytes)/1024/1024) SIZE_MB,trunc(sum(case when autoextensible=‘YES‘ then maxbytes else bytesend)/1024/1024) MAXSIZE_MBfrom dba_data_filesgroup by tablespace_name) t1,(select tablespace_name,trunc(sum(bytes)/1024/1024) FREE_MBfrom dba_free_spacegroup by tablespace_name) t2,(select tablespace_namefrom dba_tablespaceswhere contents=‘PERMANENT‘) t3where t1.tablespace_name=t2.tablespace_name and t3.tablespace_name=t1.tablespace_name and t3.tablespace_name=‘SYSTEM‘),0) as retvalue from v$database;

The Decode function is equivalent to the cash when statement, but when the condition is relatively long, the decode function is more concise.

select case            when database_role  = ‘PRIMARY‘ then  (select tname from tab where rownum=1)           else ‘0‘        end as retvalue from  v$database;

Use of Oracle decode functions