前几日，朋友发来微信，测试系统中抓到一条即将上线的SQL，严重消耗数据库资源，SQL执行计划如下：
SELECT DT,TI,ST CONT,ANCT,PIID,CID FROM
(SELECT A.ID,a.INFO,
TO_CHAR(A.DT,'YYYY')||'年'||TO_CHAR(A.DT,'MM')||'月'||TO_CHAR(A.DT,'DD')||'日' AS DT,
A.TI,
A.MED,
A.AT,
B.ID AS CONT_ID,
to_char(SUBSTR(regexp_REPLACE(B.CONT, '\s',''),1,100)) AS ST,
E.ANNT,
E.ID PIID,
ROW_NUMBER() OVER(ORDER BY A.DT DESC,A.ID DESC)AS RN
FROM NWBAS A
JOIN BASTXT B ON A.ID = B.OID
JOIN NWSANN E ON A.ID=E.OID
WHERE EXISTS(SELECT 1 FROM NWSTYP C WHERE A.ID = C.ORID AND SUBSTR(C.code,1,2) in(11,12,13))
and a.PIC=1
order by A.dt DESC
)WHERE RN <=4;
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 36016 (100)| | 4 |00:00:04.52 | 267K| 74660 |
|* 1 | VIEW | | 1 | 5237 | 36016 (1)| 00:07:13 | 4 |00:00:04.52 | 267K| 74660 |
|* 2 | WINDOW SORT PUSHED RANK | | 1 | 5237 | 36016 (1)| 00:07:13 | 5 |00:00:04.51 | 266K| 74660 |
| 3 | NESTED LOOPS | | 1 | 5237 | 36016 (1)| 00:07:13 | 67739 |00:00:04.38 | 266K| 74660 |
| 4 | NESTED LOOPS | | 1 | 5237 | 36016 (1)| 00:07:13 | 67739 |00:00:03.88 | 198K| 74660 |
|* 5 | HASH JOIN | | 1 | 5237 | 27234 (1)| 00:05:27 | 67741 |00:00:03.59 | 93944 | 74660 |
|* 6 | HASH JOIN RIGHT SEMI | | 1 | 71164 | 25527 (1)| 00:05:07 | 68836 |00:00:03.36 | 91427 | 74660 |
|* 7 | INDEX FAST FULL SCAN | NWSTYP | 1 | 71211 | 4120 (2)| 00:00:50 | 753K|00:00:02.14 | 16744 | 0 |
|* 8 | TABLE ACCESS FULL | NWSBAS | 1 | 1281K| 21403 (1)| 00:04:17 | 102K|00:00:00.78 | 74683 | 74660 |
| 9 | TABLE ACCESS FULL | NWSANN | 1 | 94294 | 653 (1)| 00:00:08 | 97456 |00:00:00.09 | 2517 | 0 |
|* 10 | INDEX UNIQUE SCAN | BASTXT | 67741 | 1 | 1 (0)| 00:00:01 | 67739 |00:00:00.26 | 104K| 0 |
| 11 | TABLE ACCESS BY INDEX ROWID| BASTXT | 67739 | 1 | 2 (0)| 00:00:01 | 67739 |00:00:00.48 | 67813 | 0 |
---------------------------------------------------------------------------------------------------------------------------------------------问题SQL单次消耗需要267K的逻辑读，大致需要4秒左右执行完成，并且执行计划中多数步骤中评估行数与实际返回行数严重不符，ID=10、11两步NESTED LOOP BASTXT表被驱动6万多次，效率低下。但是此SQL的逻辑是利用分析函数 ROW_NUMBER() OVER(ORDER BY A.DT DESC,A.ID DESC) 排序后，最终取4行而已，那么其实就是一个取Top N的SQL，可以将SQL改写为如下形式：
SELECT created,object_name,owner,namespace FROM
(SELECT /*+ INDEX_DESC(A t1_idx01) use_nl(A) use_nl(B) use_nl(C) use_nl(E) leading(A C B E)*/ A.object_id,
A.object_name,
a.CREATED,
A.owner,
B.object_id AS CONT_ID,
E.namespace,
E.OBJECT_ID PICTURE_ID
FROM T1 A
JOIN T2 B ON A.object_id = B.object_id
JOIN T3 E ON A.object_id=E.object_id
JOIN (SELECT DISTINCT D.OBJECT_ID FROM T4 D WHERE D.namespace in(29,30)) C on A.OBJECT_ID=C.OBJECT_ID
where A.TEMPORARY='N'
ORDER BY A.CREATED DESC,A.object_id DESC,A.LAST_DDL_TIME DESC --依照原SQL逻辑 order by A.LAST_DDL_TIME DESC其实可以去掉，此处加order by没有意义
) WHERE rownum <=4;create index t1_idx01 on t1(TEMPORARY,CREATED,object_id,LAST_DDL_TIME,0);
create index t2_idx02 on t2(object_id,0);
create index t3_idx03 on t3(object_id,0);
create index t4_idx01 on t4(object_id,namespace,0);
利用索引有序特性，因SQL选择性很差，大部分记录都满足SQL关联条件，理想中的执行计划利用INDEX RANGE SCAN DESCENDING降序扫描，扫描少数记录后便满足需求停止扫描 count stop by key-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 33 (100)| | 4 |00:00:00.01 | 344 | 7 |
|* 1 | COUNT STOPKEY | | 1 | | | | 4 |00:00:00.01 | 344 | 7 |
| 2 | VIEW | | 1 | 5 | 33 (0)| 00:00:01 | 4 |00:00:00.01 | 344 | 7 |
| 3 | NESTED LOOPS SEMI | | 1 | 5 | 33 (0)| 00:00:01 | 4 |00:00:00.01 | 344 | 7 |
| 4 | NESTED LOOPS | | 1 | 5 | 23 (0)| 00:00:01 | 1056 |00:00:00.01 | 331 | 4 |
| 5 | NESTED LOOPS | | 1 | 6 | 12 (0)| 00:00:01 | 1057 |00:00:00.01 | 170 | 0 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 8 | 4 (0)| 00:00:01 | 1163 |00:00:00.01 | 88 | 0 |
|* 7 | INDEX RANGE SCAN DESCENDING| T1_IDX01 | 1 | 8 | 3 (0)| 00:00:01 | 1163 |00:00:00.01 | 10 | 0 |
|* 8 | INDEX RANGE SCAN | T2_IDX02 | 1163 | 1 | 1 (0)| 00:00:01 | 1057 |00:00:00.01 | 82 | 0 |
| 9 | TABLE ACCESS BY INDEX ROWID | T3 | 1057 | 1 | 2 (0)| 00:00:01 | 1056 |00:00:00.01 | 161 | 4 |
|* 10 | INDEX RANGE SCAN | T3_IDX03 | 1057 | 1 | 1 (0)| 00:00:01 | 1056 |00:00:00.01 | 88 | 4 |
| 11 | VIEW PUSHED PREDICATE | | 1056 | 2 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 13 | 3 |
|* 12 | INDEX RANGE SCAN | T4_IDX01 | 1056 | 2 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 13 | 3 |
-----------------------------------------------------------------------------------------------------------------------------------------目标SQL优化后执行计划如下,单次执行只需要2000多个buffer，执行时间只需要几个毫秒即可
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 228 (100)| | 4 |00:00:00.05 | 2905 | 780 |
|* 1 | COUNT STOPKEY | | 1 | | | | 4 |00:00:00.05 | 2905 | 780 |
| 2 | VIEW | | 1 | 4 | 228 (0)| 00:00:03 | 4 |00:00:00.05 | 2905 | 780 |
| 3 | NESTED LOOPS SEMI | | 1 | 4 | 228 (0)| 00:00:03 | 4 |00:00:00.04 | 1631 | 0 |
| 4 | NESTED LOOPS | | 1 | 4 | 216 (0)| 00:00:03 | 8 |00:00:00.04 | 1616 | 0 |
| 5 | NESTED LOOPS | | 1 | 55 | 104 (0)| 00:00:02 | 8 |00:00:00.04 | 1595 | 0 |
| 6 | TABLE ACCESS BY INDEX ROWID| NWSBAS | 1 | 1282K| 12 (0)| 00:00:01 | 8 |00:00:00.04 | 1571 | 0 |
|* 7 | INDEX FULL SCAN DESCENDING| IDX_NWSBAS_01 | 1 | 64 | 3 (0)| 00:00:01 | 8 |00:00:00.04 | 1566 | 0 |
| 8 | TABLE ACCESS BY INDEX ROWID| BASTXT | 8 | 1 | 2 (0)| 00:00:01 | 8 |00:00:00.01 | 24 | 0 |
|* 9 | INDEX UNIQUE SCAN | BASTXT | 8 | 1 | 1 (0)| 00:00:01 | 8 |00:00:00.01 | 16 | 0 |
| 10 | TABLE ACCESS BY INDEX ROWID | NWSANN | 8 | 1 | 3 (0)| 00:00:01 | 8 |00:00:00.01 | 21 | 0 |
|* 11 | INDEX RANGE SCAN | NWSANN | 8 | 1 | 2 (0)| 00:00:01 | 8 |00:00:00.01 | 17 | 0 |
| 12 | VIEW PUSHED PREDICATE | | 8 | 1 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 15 | 0 |
|* 13 | INDEX RANGE SCAN | NWSTYP | 8 | 1 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 15 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------------