一次关于分页语句的改写优化

最新推荐文章于 2018-09-04 09:51:42 发布

jgmydsai

最新推荐文章于 2018-09-04 09:51:42 发布

阅读量1.4k

点赞数

分类专栏：改写优化

本文链接：https://blog.csdn.net/jgmydsai/article/details/16855941

版权

改写优化专栏收录该内容

28 篇文章 4 订阅

订阅专栏

一网友在群内发来语句（语句内容已做处理）

SELECT *
  FROM (SELECT
        /*这儿有d0的30多列*/
         d0.*
          FROM d0 d0
         WHERE d0.document_id IN
               (SELECT d1.document_id
                  FROM d0 d1, s2 s2
                 WHERE d1.lid = s2.id
                   AND d1.dstat = 1
                   AND d1.fid = 'xxxxxxxx'
                   AND s2.sl <= 4
                   AND (d1.d_date IS NULL OR d1.d_date > '一串数字'))
         ORDER BY d0.ct DESC)
 WHERE rownum <= 50

该语句plan如下

Execution Plan
----------------------------------------------------------
Plan hash value: 3367774531

------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                    |    50 |   181K|       | 49417   (1)| 00:09:54 |
|*  1 |  COUNT STOPKEY          |                    |       |       |       |            |          |
|   2 |   VIEW                  |                    |   141K|   500M|       | 49417   (1)| 00:09:54 |
|*  3 |    SORT ORDER BY STOPKEY|                    |   141K|    32M|    33M| 49417   (1)| 00:09:54 |
|*  4 |     HASH JOIN RIGHT SEMI|                    |   141K|    32M|  6352K| 42140   (1)| 00:08:26 |
|   5 |      VIEW               | VW_NSO_1           |   141K|  4693K|       |  1782   (1)| 00:00:22 |
|   6 |       MERGE JOIN        |                    |   141K|    11M|       |  1782   (1)| 00:00:22 |
|*  7 |        INDEX RANGE SCAN | idx3               |   141K|    10M|       |  1780   (1)| 00:00:22 |
|*  8 |        SORT JOIN        |                    |    14 |    84 |       |     2  (50)| 00:00:01 |
|*  9 |         INDEX RANGE SCAN| idx_id             |    14 |    84 |       |     1   (0)| 00:00:01 |
|  10 |      TABLE ACCESS FULL  | d0 |  1173K|   228M|       | 28047   (1)| 00:05:37 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=50)
   3 - filter(ROWNUM<=50)
   4 - access("d0"."DOCUMENT_ID"="DOCUMENT_ID")
   7 - access("d1"."fid"='xxxxxxxx')
       filter(TO_NUMBER("d1"."dstat")=1 AND
              ("d1"."d_date">一串数字 OR
              "d1"."d_date" IS NULL))
   8 - access("d1"."lid"="s2"."ID")
       filter("d1"."lid"="s2"."ID")
   9 - access("s2"."sl"<=4)


Statistics
----------------------------------------------------------
       1952  recursive calls
          0  db block gets
     104014  consistent gets
     103659  physical reads
          0  redo size
      11087  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
         50  rows processed

于是我问到d0.document_id是主建不？

网友回答：是。

于是问题来了。既然这个是主键，那么在上面的查询中，子查询里的d1完全是多余的。

大家看plan的id=4与id=8，表d0因为这种写法查询了两次。

于是我让网友把d1去掉，里面的条件放到外面来，d0作驱动走nl.

网友改后语句如下：

SELECT *
  FROM (SELECT 
        /*这儿有d0的30多列*/
         d0.*
          FROM d0 d0
         WHERE fid = 'xxxxxxxx'
           AND dstat = '1'
           AND (d0.d_date IS NULL OR d0.d_date > '一串数字')
           AND EXISTS (SELECT
                 1
                  FROM s2 s2
                 WHERE d0.lid = s2.id
                   AND s2.sl <= 4)
         ORDER BY d0.ct DESC)
 WHERE rownum <= 50

到这儿应该没什么大问题了，可网友又来信息了。

说plan不固定，一会儿逻辑读40多，一会儿4W多。

于是我让网友用

dbms_xplan.display_cursor 看plan，把查到的plan复制出来做对比。结果发现驱动表在变，有时用的是d0,有时用的是s2（其实他前面用autotrace看也一样，只是他看的时候不够仔细）。

原因找到就好办了，加hint就可以了。

网友说加了 leading()

我一看，郁闷了，他leading()里用的是表的原名，而不是别名。nl加的也不对。

把正确的发给他，最后语句如下

SELECT *
  FROM (SELECT /*+ leading(d0) */
        /*这儿有d0的30多列*/
         d0.*
          FROM d0 d0
         WHERE fid = 'xxxxxxxx'
           AND dstat = '1'
           AND (d0.d_date IS NULL OR d0.d_date > '一串数字')
           AND EXISTS (SELECT /*+ nl_sj */
                 1
                  FROM s2 s2
                 WHERE d0.lid = s2.id
                   AND s2.sl <= 4)
         ORDER BY d0.ct DESC)
 WHERE rownum <= 50

大家在加hint的时候，一定要先用简单的语句做个实验，用熟了后才可以在大的查询里用。

hint没那么神奇，随意搞个就能用。就象你把跑鞋挂腰上仍然跑不快一样。

ok。到些结束。