SQL优化之四两拨千金

向右看齐 2022-06-09 11:46 122阅读 0赞

MIS惯用分页，有的时候关联的表都非常大。如下所示的SQL，如果按照如下形式写，则会造成全表扫描。

**   原始SQL：**

SQL> SELECT \*  
    FROM (SELECT INNER\_TABLE.\*, ROWNUM OUTER\_TABLE\_ROWNUM  
            FROM (SELECT GAC.CONFIRM\_ID,  
         GAC.CONFIRM\_NAME,  
         GAC.CONFIRM\_DATE,  
         GAC.BUREAU\_ID,  
         GAC.BUREAU\_NAME,  
         GAC.CREATOR\_ID,  
         GAC.CREATOR,  
         GAC.CREATE\_DATE,  
         GAC.STATUS,  
         GAC.BID\_TARGET,  
         GAC.MEETING\_MINUTES,  
         GAC.F1,  
         GAC.F2,  
         GAC.REMARK,  
         GAC.VALIDATE\_FLAG,  
         GAC.READ\_FLAG,  
         GAC.BACK\_FLAG,  
         GAC.CONFIRM\_TYPE,  
         GAC.DATA\_AREA,  
         GAC.PROJECT\_ID,  
         GAC.PROJECT\_NAME,  
         GAC.SUBPROJECT\_ID,  
         GAC.SUBPROJECT\_NAME,  
         GAC.OLD\_CONFIRM\_ID,  
         GAC.CONFIRM\_CHANGE\_REASON,  
         GAC.IS\_STATISTIC,  
         GAC.UPDATE\_FLAG,  
         GAC.AVOID\_DUTY\_REASON,  
         GAC.LOCAL\_PROJECT\_CODE,  
         GAC.UNIQUE\_PROJECT\_CODE,  
         MAX(GACI.REQUIREMENT\_ID) AS REQUIREMENT\_ID  
    FROM GG\_ASSIGNMENT\_CONFIRM GAC, GG\_ASSIGNMENT\_CONFIRM\_ITEM GACI  
   WHERE GAC.CONFIRM\_ID = GACI.CONFIRM\_ID(+)  
     AND GAC.CREATE\_DATE >= TO\_DATE('2016-09-04', 'yyyy-mm-dd')  
     AND GAC.CREATE\_DATE < TO\_DATE('2017-09-04', 'yyyy-mm-dd') + (INTERVAL '1' DAY)  
   GROUP BY GAC.CONFIRM\_ID,  
            GAC.CONFIRM\_NAME,  
            GAC.CONFIRM\_DATE,  
            GAC.BUREAU\_ID,  
            GAC.BUREAU\_NAME,  
            GAC.CREATOR\_ID,  
            GAC.CREATOR,  
            GAC.CREATE\_DATE,  
            GAC.STATUS,  
            GAC.BID\_TARGET,  
            GAC.MEETING\_MINUTES,  
            GAC.F1,  
            GAC.F2,  
            GAC.REMARK,  
            GAC.VALIDATE\_FLAG,  
            GAC.READ\_FLAG,  
            GAC.BACK\_FLAG,  
            GAC.CONFIRM\_TYPE,  
            GAC.DATA\_AREA,  
            GAC.PROJECT\_ID,  
            GAC.PROJECT\_NAME,  
            GAC.SUBPROJECT\_ID,  
            GAC.SUBPROJECT\_NAME,  
            GAC.OLD\_CONFIRM\_ID,  
            GAC.CONFIRM\_CHANGE\_REASON,  
            GAC.IS\_STATISTIC,  
            GAC.UPDATE\_FLAG,  
            GAC.AVOID\_DUTY\_REASON,  
            GAC.LOCAL\_PROJECT\_CODE,  
            GAC.UNIQUE\_PROJECT\_CODE  
   ORDER BY GAC.CONFIRM\_TYPE ASC NULLS LAST) INNER\_TABLE  
           WHERE ROWNUM <=25) OUTER\_TABLE  
   WHERE OUTER\_TABLE\_ROWNUM >0;  
已选择25行。  
已用时间:  00: 00: 30.58  
执行计划  
\----------------------------------------------------------  
Plan hash value: 1427996981  
\-------------------------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                     | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |  
\-------------------------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT              |                             |    25 |   109K|       |   160K  (1)| 00:32:06 |       |       |  
|\*  1 |  VIEW                         |                             |    25 |   109K|       |   160K  (1)| 00:32:06 |       |       |  
|\*  2 |   COUNT STOPKEY               |                             |       |       |       |         |     |       |       |  
|   3 |    VIEW                       |                             |   589K|  2512M|       |   160K  (1)| 00:32:06 |       |       |  
|\*  4 |     SORT GROUP BY STOPKEY     |                             |   589K|   196M|   209M|   160K  (1)| 00:32:06 |       |       |  
|\*  5 |      HASH JOIN OUTER          |                             |   589K|   196M|    32M|   116K  (1)| 00:23:17 |       |       |  
|   6 |       PARTITION RANGE ITERATOR|                             |   105K|    31M|       |  3310   (1)| 00:00:40 |     7 |     8 |  
|   7 |        PARTITION LIST ALL     |                             |   105K|    31M|       |  3310   (1)| 00:00:40 |     1 |    27 |  
|\*  8 |         TABLE ACCESS FULL     | GG\_ASSIGNMENT\_CONFIRM       |   105K|    31M|       |  3310   (1)| 00:00:40 |   163 |   216 |  
|   9 |       PARTITION RANGE ALL     |                             |  3276K|   112M|       |   103K  (1)| 00:20:48 |     1 |1048575|  
|  10 |        PARTITION LIST ALL     |                             |  3276K|   112M|       |   103K  (1)| 00:20:48 |     1 |    27 |  
|  11 |         TABLE ACCESS FULL     | GG\_ASSIGNMENT\_CONFIRM\_ITEM  |  3276K|   112M|       |   103K  (1)| 00:20:48 |     1 |1048575|  
\-------------------------------------------------------------------------------------------------------------------------------------  
Predicate Information (identified by operation id):  
\---------------------------------------------------  
   1 - filter("OUTER\_TABLE\_ROWNUM">0)  
   2 - filter(ROWNUM<=25)  
   4 - filter(ROWNUM<=25)  
   5 - access("GAC"."CONFIRM\_ID"="GACI"."CONFIRM\_ID"(+))  
   8 - filter("GAC"."CREATE\_DATE">=TO\_DATE(' 2016-09-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND  
              "GAC"."CREATE\_DATE"<TO\_DATE(' 2017-09-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))  
统计信息  
\----------------------------------------------------------  
          0  recursive calls  
          0  db block gets  
     374591  consistent gets  
     307518  physical reads  
      14208  redo size  
       6776  bytes sent via SQL\*Net to client  
        483  bytes received via SQL\*Net from client  
          3  SQL\*Net roundtrips to/from client  
          1  sorts (memory)  
          0  sorts (disk)  
         25  rows processed  
  
**优化后：四两拨千金就是先把主表的数据先查出25条，然后跟从表关联，这是一种很重要的优化思路。**  
SQL> with GAC as (SELECT \*  
    FROM (SELECT INNER\_TABLE.\*, ROWNUM OUTER\_TABLE\_ROWNUM  
            FROM (SELECT GAC.CONFIRM\_ID,  
         GAC.CONFIRM\_NAME,  
         GAC.CONFIRM\_DATE,  
         GAC.BUREAU\_ID,  
         GAC.BUREAU\_NAME,  
         GAC.CREATOR\_ID,  
         GAC.CREATOR,  
         GAC.CREATE\_DATE,  
         GAC.STATUS,  
         GAC.BID\_TARGET,  
         GAC.MEETING\_MINUTES,  
         GAC.F1,  
         GAC.F2,  
         GAC.REMARK,  
         GAC.VALIDATE\_FLAG,  
         GAC.READ\_FLAG,  
         GAC.BACK\_FLAG,  
         GAC.CONFIRM\_TYPE,  
         GAC.DATA\_AREA,  
         GAC.PROJECT\_ID,  
         GAC.PROJECT\_NAME,  
         GAC.SUBPROJECT\_ID,  
         GAC.SUBPROJECT\_NAME,  
         GAC.OLD\_CONFIRM\_ID,  
         GAC.CONFIRM\_CHANGE\_REASON,  
         GAC.IS\_STATISTIC,  
         GAC.UPDATE\_FLAG,  
         GAC.AVOID\_DUTY\_REASON,  
         GAC.LOCAL\_PROJECT\_CODE,  
         GAC.UNIQUE\_PROJECT\_CODE  
    FROM GG\_ASSIGNMENT\_CONFIRM GAC  
   WHERE GAC.CREATE\_DATE >= TO\_DATE('2016-09-04', 'yyyy-mm-dd')  
     AND GAC.CREATE\_DATE < TO\_DATE('2017-09-04', 'yyyy-mm-dd') + (INTERVAL '1' DAY)  
     order by GAC.CREATE\_DATE desc) INNER\_TABLE  
           WHERE ROWNUM <=25) OUTER\_TABLE  
   WHERE OUTER\_TABLE\_ROWNUM >0)  
  select \* from (select GAC.\*,  
         row\_number() over(partition by GAC.Confirm\_Id order by REQUIREMENT\_ID desc) rn,  
         GACI.REQUIREMENT\_ID AS REQUIREMENT\_ID  
    FROM GAC, GG\_ASSIGNMENT\_CONFIRM\_ITEM GACI  
   WHERE GAC.CONFIRM\_ID = GACI.CONFIRM\_ID) where rn=1  
     order by CREATE\_DATE desc;  
已选择25行。  
已用时间:  00: 00: 00.04  
执行计划  
\----------------------------------------------------------  
Plan hash value: 1757804957  
\-----------------------------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                                 | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
\-----------------------------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                          |                             |   140 |   614K|   112   (2)| 00:00:02 |       |       |  
|   1 |  SORT ORDER BY                            |                             |   140 |   614K|   112   (2)| 00:00:02 |       |       |  
|\*  2 |   VIEW                                    |                             |   140 |   614K|   111   (1)| 00:00:02 |       |       |  
|\*  3 |    WINDOW SORT PUSHED RANK                |                             |   140 |   614K|   111   (1)| 00:00:02 |       |       |  
|   4 |     NESTED LOOPS                          |                             |   140 |   614K|   110   (0)| 00:00:02 |       |       |  
|\*  5 |      VIEW                                 |                             |    25 |   108K|    10   (0)| 00:00:01 |       |       |  
|\*  6 |       COUNT STOPKEY                       |                             |       |       |         |             |       |       |  
|   7 |        VIEW                               |                             |    25 |   108K|    10   (0)| 00:00:01 |       |       |  
|   8 |         TABLE ACCESS BY GLOBAL INDEX ROWID| GG\_ASSIGNMENT\_CONFIRM       |   105K|    31M|    10   (0)| 00:00:01 | ROWID | ROWID |  
|\*  9 |          INDEX RANGE SCAN DESCENDING      | INDEX\_CREATE\_DATE\_AC\_12     |    25 |       |     3   (0)| 00:00:01 |       |       |  
|  10 |      TABLE ACCESS BY GLOBAL INDEX ROWID   | GG\_ASSIGNMENT\_CONFIRM\_ITEM  |     6 |   216 |     4   (0)| 00:00:01 | ROWID | ROWID |  
|\* 11 |       INDEX RANGE SCAN                    | IDX\_GG\_ASS\_CONF\_ID\_12       |     6 |       |     2   (0)| 00:00:01 |       |       |  
\-----------------------------------------------------------------------------------------------------------------------------------------  
Predicate Information (identified by operation id):  
\---------------------------------------------------  
   2 - filter("RN"=1)  
   3 - filter(ROW\_NUMBER() OVER ( PARTITION BY "OUTER\_TABLE"."CONFIRM\_ID" ORDER BY INTERNAL\_FUNCTION("GACI"."REQUIREMENT\_ID")  
              DESC )<=1)  
   5 - filter("OUTER\_TABLE\_ROWNUM">0)  
   6 - filter(ROWNUM<=25)  
   9 - access("GAC"."CREATE\_DATE">=TO\_DATE(' 2016-09-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "GAC"."CREATE\_DATE"<TO\_DATE('  
              2017-09-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))  
  11 - access("OUTER\_TABLE"."CONFIRM\_ID"="GACI"."CONFIRM\_ID")  
统计信息  
\----------------------------------------------------------  
          0  recursive calls  
          0  db block gets  
        295  consistent gets  
          0  physical reads  
          0  redo size  
       8139  bytes sent via SQL\*Net to client  
        483  bytes received via SQL\*Net from client  
          3  SQL\*Net roundtrips to/from client  
          2  sorts (memory)  
          0  sorts (disk)  
         25  rows processed