Oracle case when rewrites SQL

Oracle case when rewrites SQL

-explanation: the case comes from "harvest, not just SQL optimization".

Create test data:

SQL > drop table T1 purge

SQL > drop table T2 purge

SQL > create table T1 as select * from dba_objects

SQL > create table T2 as select * from dba_objects

SQL > update T2 set status = 'INVALID' WHERE ROWNUM update T2 set generated =' Y 'WHERE ROWNUM update T2 set temporary =' Y' WHERE ROWNUM update T2 set temporary ='M 'WHERE temporary' Y'

SQL > update T2 set temporary ='Q' WHERE temporary'Y' or temporary'M'

SQL > COMMIT

SQL > set autotrace traceonly

SQL > set linesize 1000

Original SQL:

SQL > select t1.object_name, t1.object_id, (select count (*) from T2 where temporary ='Y' and t2.object_id = t1.object_id) CNT_TEMPORARY_Y, (select count (*) from T2 where created > = sysdate-365 and t2.object_id = t1.object_id) CNT_CREATED_NEW (select sum (object_id) from T2 where status' VALUD' and t2.object_id = t1.object_id) SUM_OBJID_STATUS_V, (select sum (object_id) from T2 where generated ='Y' and t2.object_id = t1.object_id) SUM_OBJID_GENERATED_Y (select sum (object_id) from T2 where generated ='M 'and t2.object_id = t1.object_id) SUM_OBJID_GENERATED_M, (select sum (object_id) from T2 where generated =' Q' and t2.object_id = t1.object_id) SUM_OBJID_GENERATED_Q from T1 where t1.object_id = sysdate-365 then 1 end) CNT_CREATED_NEW Sum (case when t2.statusqualified VALID 'then t2.object_id end) SUM_OBJID_STATUS_V,sum (case when t2.generated =' Y' then t2.object_id end) SUM_OBJID_GENERATED_Y,sum (case when t2.generated ='M' then t2.object_id end) SUM_OBJID_GENERATED_M,sum (case when t2.generated ='Q' then t2.object_id end) SUM_OBJID_GENERATED_Qfrom t2group by t2.object_id) select t1.objectobjectname Wrestling 2.* from T1 recorder wintert2where t1.object_id=w_t2.object_idand t1.object_id