Top N需求SQL优化提升案例

前几日,朋友发来微信,测试系统中抓到一条即将上线的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 |
--------------------------------------------------------------------------------------------------------------------------------------------------