Oracle case when改写SQL

---说明：案例来自《收获，不止SQL优化》

创建测试数据：

SQL>droptablet1 purge;

SQL>droptablet2 purge;

SQL>createtablet1 asselect*fromdba_objects ;

SQL>createtablet2 asselect*fromdba_objects ;

SQL>updatet2 setstatus='INVALID'WHEREROWNUM<=10000;

SQL>updatet2 setgenerated='Y'WHEREROWNUM<=10000;

SQL>updatet2 settemporary='Y'WHEREROWNUM<=10000;

SQL>updatet2 settemporary='M'WHEREtemporary<>'Y';

SQL>updatet2 settemporary='Q'WHEREtemporary<>'Y'ortemporary<>'M';

SQL>COMMIT;

SQL>setautotrace traceonly

SQL>setlinesize 1000

原SQL：

SQL>selectt1.object_name,t1.object_id,(selectcount(*)fromt2wheretemporary='Y'andt2.object_id=t1.object_id)CNT_TEMPORARY_Y,(selectcount(*)fromt2wherecreated>=sysdate-365andt2.object_id=t1.object_id)CNT_CREATED_NEW,(selectsum(object_id)fromt2wherestatus<>'VALUD'andt2.object_id=t1.object_id)SUM_OBJID_STATUS_V,(selectsum(object_id)fromt2wheregenerated='Y'andt2.object_id=t1.object_id)SUM_OBJID_GENERATED_Y,(selectsum(object_id)fromt2wheregenerated='M'andt2.object_id=t1.object_id)SUM_OBJID_GENERATED_M,(selectsum(object_id)fromt2wheregenerated='Q'andt2.object_id=t1.object_id)SUM_OBJID_GENERATED_Qfromt1wheret1.object_id<=50;

case when改造后的SQL：

withw_t2as(selectt2.object_id,count(casewhent2.temporary='Y'then1end)CNT_TEMPORARY_Y,count(casewhencreated>=sysdate-365then1end)CNT_CREATED_NEW,sum(casewhent2.status<>'VALID'thent2.object_idend)SUM_OBJID_STATUS_V,sum(casewhent2.generated='Y'thent2.object_idend)SUM_OBJID_GENERATED_Y,sum(casewhent2.generated='M'thent2.object_idend)SUM_OBJID_GENERATED_M,sum(casewhent2.generated='Q'thent2.object_idend)SUM_OBJID_GENERATED_Qfromt2groupbyt2.object_id)selectt1.object_name,t1.object_id,w_t2.*fromt1,w_t2wheret1.object_id=w_t2.object_idandt1.object_id<=50;

结论：SQL改写后T2表访问次数由6次降到1次，逻辑读consistent gets由320100降到2580，性能有所提升。

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！