Oracle rownum 分页引起的效率问题及优化思路

时间：2014-12-16 02:22:17 阅读：419 评论：0 收藏：0 [点我收藏+]

链接：http://blog.itpub.net/28602568/viewspace-1366015/

前言：

前不久通过查询每天执行慢的SQL 发现一个sql cost =299 执行了65s【cost很大执行未必很慢，但cost小执行很慢就肯定有问题,,个人觉得统计信息没问题时，cost 大也要着重优化下..】

第一感觉应该是执行计划走错了...带着疑问向下验证是什么原因导致生成了慢的执行计划呢？

SQL：

select *
  from (SELECT EMP.UNIQ_NO,
               EMP.EMP_NAME,
               EMP.SEX,
               CER.CARD_TYPE,
               CER.CARD_NUM,
               EMP.IS_BEIJING ENTRYTYPE,
               EMP.EMP_TYPE,
               EMP.HOUSEHOLD_ADDR
          FROM ES_EMP_INFO EMP, ES_EMP_CER CER
         WHERE 1 = 1
           AND EMP.UNIQ_NO = CER.UNIQ_NO(+)
           AND CER.IS_APP_UNIQ_NO(+) = 1
           AND CER.IS_VALID(+) = 1
           AND EXISTS
         (SELECT 1
                  FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD
                 WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID
                   AND LATION.IS_VALID = 1
                   AND HOLD.IS_VALID = 1
                   AND HOLD.DOOR_STATE = 1
                   AND LATION.UNIQ_NO = EMP.UNIQ_NO)
           AND NOT EXISTS
         (SELECT 1 FROM CS_ORDER CO WHERE CO.UNIQ_NO = EMP.UNIQ_NO)
         ORDER BY EMP.UNIQ_NO)
where rownum <= 1;

大概情况：

表	总行数	字段名/关系类型	num_distinct
CS_ORDER	621122	UNIQ_NO-->普通索引	583936
ES_EMP_CER	2588497	UNIQ_NO-->普通索引	1611008
ES_EMP_HDRELATION	49862	UNIQ_NO-->主键	30282
ES_EMP_HDRELATION	49862	CEN_INFO_ID -->ES_EMP_HDRELATION外键	49862
ES_EMP_HOUSEHOLD	49862	CEN_INFO_ID-->主键	49862
ES_EMP_INFO	1605350	UNIQ_NO-->主键	1605350

诡异之处：

单独查询内层的sql 2s ，加一层where rownum <=1 执行65s； --> 理论上会觉得where rownum是内层查询完之后再过滤的....到底是什么引起的呢？

那我们分别看下带不带rownum 的执行计划和执行时间

带rownum <= 1 的执行计划和执行时间

SQL> SET AUTOT on -- /*TRACEONLY */

SQL> set line 100

SQL> set timing on

SQL> select UNIQ_NO /*取主键值仅测试*/

2    from ( SELECT EMP.UNIQ_NO,

3                 EMP.EMP_NAME,

4                 EMP.SEX,

5                 CER.CARD_TYPE,

6                 CER.CARD_NUM,

7                 EMP.IS_BEIJING ENTRYTYPE,

8                 EMP.EMP_TYPE,

9                 EMP.HOUSEHOLD_ADDR

10            FROM ES_EMP_INFO EMP, ES_EMP_CER CER

11           WHERE 1 = 1

12             AND EMP.UNIQ_NO = CER.UNIQ_NO(+)

13             AND CER.IS_APP_UNIQ_NO(+) = 1

14             AND CER.IS_VALID(+) = 1

15             AND EXISTS

16           ( SELECT 1

17                    FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD

18                   WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID

19                     AND LATION.IS_VALID = 1

20                     AND HOLD.IS_VALID = 1

21                     AND HOLD.DOOR_STATE = 1

22                     AND LATION.UNIQ_NO = EMP.UNIQ_NO)

23             AND NOT EXISTS

24           ( SELECT 1 FROM CS_ORDER CO WHERE CO.UNIQ_NO = EMP.UNIQ_NO)

25           ORDER BY EMP.UNIQ_NO)

26   where rownum <= 1;

UNIQ_NO

----------

      4033

已用时间:  00: 01: 05.94

执行计划

----------------------------------------------------------

Plan hash value: 532298994

---------------------------------------------------------------------------------------------------------

|Id | Operation                        | Name                 | Rows | Bytes | Cost (%CPU )| Time    |

---------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT                  |                      |     1 |    13 |   299    (3)| 00:00 :04 |

|* 1 |  COUNT STOPKEY                   |                      |       |       |            |    |

| 2 |   VIEW                           |                      |     1 |    13 |   299    (3)| 00:00 :04 |

| 3 |    NESTED LOOPS SEMI             |                      |     1 |    75 |   299    (3)| 00:00 :04 |

| 4 |     NESTED LOOPS ANTI            |                      |     1 |    69 |    16    (0)| 00:00 :01 |

| 5 |      NESTED LOOPS OUTER           |                      |     2 |   126 |    12    (0)| 00:00 :01 |

| 6 |       INDEX FULL SCAN            | PK_ES_EMP_INFO       |     2 |       |     3    (0)| 00:00 :01 |

|* 7 |       TABLE ACCESS BY INDEX ROWID | ES_EMP_CER           |     1 |    34 |     4    (0)| 00:00 :01 |

|* 8 |        INDEX RANGE SCAN          | RE_EMP_CER_FK        |     2 |       |     2    (0)| 00:00 :01 |

|* 9 |      INDEX RANGE SCAN            | IDX_CS_ORDER_UNIQ_NO |   177K|  1040 K|     2   ( 0)| 00 :00: 01 |

|*10 |     VIEW                         | VW_SQ_1              |  8310 | 49860 |   283    (3)| 00:00 :04 |

|*11 |      HASH JOIN                    |                      |  8310 |   202K|   283    (3)| 00:00 :04 |

|*12 |       TABLE ACCESS FULL          | ES_EMP_HOUSEHOLD     |  8310 | 91410 |   231    (3)| 00:00 :03 |

|*13 |       TABLE ACCESS FULL          | ES_EMP_HDRELATION    | 24931 |   340K|    51    (4)| 00:00 :01 |

---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<=1)

   7 - filter("CER"."IS_APP_UNIQ_NO"(+)=1 AND "CER"."IS_VALID"(+)=1)

   8 - access("EMP"."UNIQ_NO"="CER"."UNIQ_NO"(+))

   9 - access("CO"."UNIQ_NO"="EMP"."UNIQ_NO")

10 - filter("ITEM_1"="EMP"."UNIQ_NO")

11 - access("LATION"."CEN_INFO_ID"="HOLD"."CEN_INFO_ID")

12 - filter("HOLD"."DOOR_STATE"=1 AND "HOLD"."IS_VALID"=1)

13 - filter("LATION"."IS_VALID"=1)

统计信息

----------------------------------------------------------

          1 recursive calls

       6223 db block gets

    3540228 consistent gets

          0 physical reads

          0 redo size

        235 bytes sent via SQL*Net to client

        251 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

SQL>

不带rownum <= 1 的执行计划和执行时间

SQL> SELECT EMP.UNIQ_NO/*,

2         EMP.EMP_NAME,

3         EMP.SEX,

4         CER.CARD_TYPE,

5         CER.CARD_NUM,

6         EMP.IS_BEIJING ENTRYTYPE,

7         EMP.EMP_TYPE,

8         EMP.HOUSEHOLD_ADDR*/

9    FROM ES_EMP_INFO EMP, ES_EMP_CER CER

10   WHERE 1 = 1

11     AND EMP.UNIQ_NO = CER.UNIQ_NO(+)

12     AND CER.IS_APP_UNIQ_NO(+) = 1

13     AND CER.IS_VALID(+) = 1

14     AND EXISTS (SELECT 1

15            FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD

16           WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID

17             AND LATION.IS_VALID = 1

18             AND HOLD.IS_VALID = 1

19             AND HOLD.DOOR_STATE = 1

20             AND LATION.UNIQ_NO = EMP.UNIQ_NO)

21     AND NOT EXISTS

22   (SELECT 1 FROM CS_ORDER CO WHERE CO.UNIQ_NO = EMP.UNIQ_NO)

23   ORDER BY EMP.UNIQ_NO;

  UNIQ_NO

----------

      4033

.......

2202 rows selected.

Elapsed: 00:00:00.83

Execution Plan

----------------------------------------------------------

Plan hash value: 1443858248

-------------------------------------------------------------------------------------------------

|Id | Operation                | Name                 | Rows | Bytes | Cost (%CPU )| Time     |

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT          |                      |  5263 |   154K| 10408   (3)| 00:02 :05 |

| 1 |  SORT ORDER BY           |                      |  5263 |   154K| 10408   (3)| 00:02 :05 |

|* 2 |   HASH JOIN ANTI         |                      |  5263 |   154K| 10407   (3)| 00:02 :05 |

|* 3 |    HASH JOIN OUTER       |                      |  8310 |   194K|  9998   (3)| 00:02 :00 |

|* 4 |     HASH JOIN RIGHT SEMI |                      |  8310 | 99720 |  1271 (5 )| 00: 00:16 |

| 5 |      VIEW                | VW_SQ_1              |  8310 | 49860 |   283 (3 )| 00: 00:04 |

|* 6 |       HASH JOIN           |                      |  8310 |   202K|   283   (3)| 00:00 :04 |

|* 7 |        TABLE ACCESS FULL | ES_EMP_HOUSEHOLD     |  8310 | 91410 |   231 (3 )| 00: 00:03 |

|* 8 |        TABLE ACCESS FULL | ES_EMP_HDRELATION    | 24931 |   340K|    51   (4)| 00:00 :01 |

| 9 |      INDEX FAST FULL SCAN| PK_ES_EMP_INFO       |  1605K|  9406K|   968   (3)| 00:00 :12 |

|*10 |     TABLE ACCESS FULL    | ES_EMP_CER           |  1046K|    11M|  8714   (3)| 00:01 :45 |

| 11 |    INDEX FAST FULL SCAN | IDX_CS_ORDER_UNIQ_NO |   621K|  3639K|   401   (3)| 00:00 :05 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("CO"."UNIQ_NO"="EMP"."UNIQ_NO")

   3 - access("EMP"."UNIQ_NO"="CER"."UNIQ_NO"(+))

   4 - access("ITEM_1"="EMP"."UNIQ_NO")

   6 - access("LATION"."CEN_INFO_ID"="HOLD"."CEN_INFO_ID")

   7 - filter("HOLD"."DOOR_STATE"=1 AND "HOLD"."IS_VALID"=1)

   8 - filter("LATION"."IS_VALID"=1)

10 - filter("CER"."IS_APP_UNIQ_NO"(+)=1 AND "CER"."IS_VALID"(+)=1)

Statistics

----------------------------------------------------------

          0 recursive calls

          7 db block gets

      37418 consistent gets

          0 physical reads

          0 redo size

      41179 bytes sent via SQL*Net to client

       2130 bytes received via SQL*Net from client

        148 SQL*Net roundtrips to/from client

          1 sorts (memory)

          0 sorts (disk)

       2202 rows processed 如下是1条数据的测试，和该sql寓意相同，且rownum rn 可以理解一个字段...

SQL> select UNIQ_NO

2    from (SELECT c.*, rownum rn

3            FROM (SELECT EMP.UNIQ_NO,

4                         EMP.EMP_NAME,

5                         EMP.SEX,

6                         CER.CARD_TYPE,

7                         CER.CARD_NUM,

8                         EMP.IS_BEIJING ENTRYTYPE,

9                         EMP.EMP_TYPE,

10                         EMP.HOUSEHOLD_ADDR

11                    FROM ES_EMP_INFO EMP, ES_EMP_CER CER

12                   WHERE 1 = 1

13                     AND EMP.UNIQ_NO = CER.UNIQ_NO(+)

14                     AND CER.IS_APP_UNIQ_NO(+) = 1

15                     AND CER.IS_VALID(+) = 1

16                     AND EXISTS

17                   (SELECT 1

18                            FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD

19                           WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID

20                             AND LATION.IS_VALID = 1

21                             AND HOLD.IS_VALID = 1

22                             AND HOLD.DOOR_STATE = 1

23                             AND LATION.UNIQ_NO = EMP.UNIQ_NO)

24                     AND NOT EXISTS

25                   (SELECT 1

26                            FROM CS_ORDER CO

27                           WHERE CO.UNIQ_NO = EMP.UNIQ_NO)

28                   ORDER BY EMP.UNIQ_NO) c)

29   where rn <= 1;

UNIQ_NO

----------

      4033

已用时间: 00: 00: 02.13

执行计划

----------------------------------------------------------

Plan hash value: 1562207150

---------------------------------------------------------------------------------------------------

|Id | Operation                  | Name                 | Rows | Bytes | Cost (%CPU )| Time     |

---------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT            |                      |  5263 |   133K| 23551    (3)| 00:04 :43 |

|* 1 |  VIEW                      |                      |  5263 |   133K| 23551    (3)| 00:04 :43 |

| 2 |   COUNT                    |                      |       |       |            |          |

| 3 |    VIEW                    |                      |  5263 | 68419 | 23551    (3)| 00:04 :43 |

| 4 |     SORT ORDER BY          |                      |  5263 |   385K| 23551    (3)| 00:04 :43 |

|* 5 |      HASH JOIN ANTI        |                      |  5263 |   385K| 23549    (3)| 00:04 :43 |

|* 6 |       HASH JOIN OUTER      |                      |  8310 |   559K| 23140    (3)| 00:04 :38 |

|* 7 |        HASH JOIN RIGHT SEMI|                      |  8310 |   284K| 14413    (3)| 00:02 :53 |

| 8 |         VIEW               | VW_SQ_1              |  8310 | 49860 |   283    (3)| 00:00 :04 |

|* 9 |          HASH JOIN          |                      |  8310 |   202K|   283    (3)| 00:00 :04 |

|*10 |           TABLE ACCESS FULL| ES_EMP_HOUSEHOLD     |  8310 | 91410 |   231    (3)| 00:00 :03 |

|*11 |           TABLE ACCESS FULL| ES_EMP_HDRELATION    | 24931 |   340K|    51    (4)| 00:00 :01 |

| 12 |         TABLE ACCESS FULL | ES_EMP_INFO          |  1605K|    44M| 14110    (2)| 00:02 :50 |

|*13 |        TABLE ACCESS FULL   | ES_EMP_CER           |  1046K|    33M|  8714    (3)| 00:01 :45 |

| 14 |       INDEX FAST FULL SCAN | IDX_CS_ORDER_UNIQ_NO |   621K|  3639K|   401    (3)| 00:00 :05 |

---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"<=1)

   5 - access("CO"."UNIQ_NO"="EMP"."UNIQ_NO")

   6 - access("EMP"."UNIQ_NO"="CER"."UNIQ_NO"(+))

   7 - access("ITEM_1"="EMP"."UNIQ_NO")

   9 - access("LATION"."CEN_INFO_ID"="HOLD"."CEN_INFO_ID")

10 - filter("HOLD"."DOOR_STATE"=1 AND "HOLD"."IS_VALID"=1)

11 - filter("LATION"."IS_VALID"=1)

13 - filter("CER"."IS_APP_UNIQ_NO"(+)=1 AND "CER"."IS_VALID"(+)=1)

统计信息

----------------------------------------------------------

          1 recursive calls

          6 db block gets

      85800 consistent gets

       4462 physical reads

          0 redo size

        235 bytes sent via SQL*Net to client

        251 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          1 sorts (memory)

          0 sorts (disk)

          1 rows processed

SQL>

总结：
可以看出加了where rownum<=1 后，更改了内层的执行计划；

？什么是rownum

rownum是个伪列，会随着返回结果集依次递加生成值(如返回的第一条返回的结果=1；第二条=2....) 没有1就不会有2；

？为什么rownum更改内层的执行计划

推测：

如果 order by 排序的字段恰巧是索引尤其是主键时[简单理解回表性能极小]；

且 oracle会衡量得到rownum=n 和不加(rownum=n)性能哪个要好 [不考虑数据匹配情况，只考虑范围的性能] -- 下文有验证 ①

恰巧有该sql 情况rownum<=n 的n极小，就会引起的oracle先找到n行排序再去关联，

如果n个数据与内层关联都能匹配那对性能有很大帮助，但如果每次取出n行数据与内层关联没有得到正确数据，将继续循环....直到有数据也就是直到满足rownum=n 行结束.-- 下文有验证 ②

可以通过几种方式解决：

1、更改分页方式： --简单理解成一个正常字段，会和源内层sql执行计划相同，也不会用到rownum的特点..

①、select * from (select t.* ,rownum rn from t order by 1) where rn<=1;

②、SELECT * ( SELECT t.*, row_number() over (ORDER BY UNIQ_NO) rn from t) where rn <= 1;

2、优化sql,走索引/主键索引，避免sql执行时间稳定最好优化后性能消耗小于加where rownum=1的性能【见优化后的sql】

优化后的sql

SQL> select UNIQ_NO

2    from ( SELECT EMP.UNIQ_NO,

3                 EMP.EMP_NAME,

4                 EMP.SEX,

5                 CER.CARD_TYPE,

6                 CER.CARD_NUM,

7                 EMP.IS_BEIJING ENTRYTYPE,

8                 EMP.EMP_TYPE,

9                 EMP.HOUSEHOLD_ADDR

10            FROM ES_EMP_INFO EMP, ES_EMP_CER CER

11           WHERE 1 = 1

12             AND EMP.UNIQ_NO = CER.UNIQ_NO(+)

13             AND CER.IS_APP_UNIQ_NO(+) = 1

14             AND CER.IS_VALID(+) = 1

15             AND EMP.UNIQ_NO in

16                 ( SELECT LATION.UNIQ_NO

17                    FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD

18                   WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID

19                     AND LATION.IS_VALID = 1

20                     AND HOLD.IS_VALID = 1

21                     AND HOLD.DOOR_STATE = 1

22                     and LATION.UNIQ_NO not in

23                         ( SELECT CO.UNIQ_NO

24                            FROM CS_ORDER CO

25                           where CO.UNIQ_NO is not null /*不加UNIQ_NO is not null 不会走索引,∵索引不记录空

26           ORDER BY EMP.UNIQ_NO)

27   where rownum <= 1 ;

   UNIQ_NO

----------

      4033

已用时间: 00: 00: 00.31

执行计划

----------------------------------------------------------

Plan hash value: 2640007952

-----------------------------------------------------------------------------------------------------------

|Id | Operation                          | Name                 | Rows | Bytes | Cost (%CPU )| Time     |

-----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT                    |                      |     1 |    13 |  1193   ( 3)| 00 :00: 15 |

|* 1 |  COUNT STOPKEY                     |                      |       |       |            |      |

| 2 |   VIEW                             |                      |    96 |  1248 |  1193   ( 3)| 00 :00: 15 |

|* 3 |    SORT ORDER BY STOPKEY           |                      |    96 | 45600 |  1193   ( 3)| 00 :00: 15 |

| 4 |     VIEW                           | VM_NWVW_2            |    96 | 45600 |  1193   ( 3)| 00 :00: 15 |

| 5 |      HASH UNIQUE                    |                      |    96 | 11328 |  1193   ( 3)| 00 :00: 15 |

| 6 |       NESTED LOOPS OUTER            |                      |    96 | 11328 |  1192   ( 3)| 00 :00: 15 |

| 7 |        NESTED LOOPS                |                      |    83 |  5976 |   860   ( 4)| 00 :00: 11 |

|* 8 |         HASH JOIN ANTI             |                      |    83 |  2573 |   693   ( 4)| 00 :00: 09 |

|* 9 |          HASH JOIN                  |                      |  8310 |   202 K|   283   ( 3)| 00 :00: 04 |

|*10 |           TABLE ACCESS FULL        | ES_EMP_HOUSEHOLD     |  8310 | 91410 |   231   ( 3)| 00 :00: 03 |

|*11 |           TABLE ACCESS FULL        | ES_EMP_HDRELATION    | 24931 |   340 K|    51   ( 4)| 00 :00: 01 |

|*12 |          INDEX FAST FULL SCAN      | IDX_CS_ORDER_UNIQ_NO |   620K|  3635 K|   403   ( 3)| 00 :00: 05 |

| 13 |         TABLE ACCESS BY INDEX ROWID | ES_EMP_INFO          |     1 |    41 |     2   ( 0)| 00 :00: 01 |

|*14 |          INDEX UNIQUE SCAN         | PK_ES_EMP_INFO       |     1 |       |     1   ( 0)| 00 :00: 01 |

|*15 |        TABLE ACCESS BY INDEX ROWID | ES_EMP_CER           |     1 |    46 |     4   ( 0)| 00 :00: 01 |

|*16 |         INDEX RANGE SCAN           | RE_EMP_CER_FK        |     2 |       |     2   ( 0)| 00 :00: 01 |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<=1)

   3 - filter(ROWNUM<=1)

   8 - access("LATION"."UNIQ_NO"="CO"."UNIQ_NO")

   9 - access("LATION"."CEN_INFO_ID"="HOLD"."CEN_INFO_ID")

10 - filter("HOLD"."DOOR_STATE"=1 AND "HOLD"."IS_VALID"=1)

11 - filter("LATION"."IS_VALID"=1)

12 - filter("CO"."UNIQ_NO" IS NOT NULL)

14 - access("EMP"."UNIQ_NO"="LATION"."UNIQ_NO")

15 - filter("CER"."IS_APP_UNIQ_NO"(+)=1 AND "CER"."IS_VALID"(+)=1)

16 - access("EMP"."UNIQ_NO"="CER"."UNIQ_NO"(+))

统计信息

----------------------------------------------------------

          2 recursive calls

          4 db block gets

      18622 consistent gets

         24 physical reads

        104 redo size

        235 bytes sent via SQL*Net to client

        251 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          1 sorts (memory)

          0 sorts (disk)

          1 rows processed

SQL>

优化思路：

通过上文发现cost =299 执行65s 是因为rownum导致的，所以我们优化要依据 ‘不带rownum <= 1 的执行计划和执行时间’来作为参考

如下：

执行计划：

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 5263 | 133 K| 23551 ( 3)| 00 :04 : 43 |

|* 1 | VIEW | | 5263 | 133 K| 23551 ( 3)| 00 :04 : 43 |

| 2 | COUNT | | | | | |

| 3 | VIEW | | 5263 | 68419 | 23551 ( 3)| 00 :04 : 43 |

| 4 | SORT ORDER BY | | 5263 | 385 K| 23551 ( 3)| 00 :04 : 43 |

|* 5 | HASH JOIN ANTI | | 5263 | 385 K| 23549 ( 3)| 00 :04 : 43 |

|* 6 | HASH JOIN OUTER | | 8310 | 559 K| 23140 ( 3)| 00 :04 : 38 |

|* 7 | HASH JOIN RIGHT SEMI| | 8310 | 284 K| 14413 ( 3)| 00 :02 : 53 |

| 8 | VIEW | VW_SQ_1 | 8310 | 49860 | 283 ( 3)| 00 :00 : 04 |

|* 9 | HASH JOIN | | 8310 | 202 K| 283 ( 3)| 00 :00 : 04 |

|* 10 | TABLE ACCESS FULL| ES_EMP_HOUSEHOLD | 8310 | 91410 | 231 ( 3)| 00 :00 : 03 |

|* 11 | TABLE ACCESS FULL| ES_EMP_HDRELATION | 24931 | 340 K| 51 ( 4)| 00 :00 : 01 |

| 12 | TABLE ACCESS FULL | ES_EMP_INFO | 1605 K| 44 M| 14110 ( 2)| 00 :02 : 50 |

|* 13 | TABLE ACCESS FULL | ES_EMP_CER | 1046 K| 33 M| 8714 ( 3)| 00 :01 : 45 |

| 14 | INDEX FAST FULL SCAN | IDX_CS_ORDER_UNIQ_NO | 621K| 3639 K| 401 ( 3)| 00 :00 : 05 |

---------------------------------------------------------------------------------------------------

表大概情况..忽略较小行数差距

表	总行数	字段名/关系类型	num_distinct
CS_ORDER	621122	UNIQ_NO-->普通索引	583936
ES_EMP_CER	2588497	UNIQ_NO-->普通索引	1611008
ES_EMP_HDRELATION	49862	UNIQ_NO-->主键	30282
ES_EMP_HDRELATION	49862	CEN_INFO_ID-->ES_EMP_HDRELATION外键	49862
ES_EMP_HOUSEHOLD	49862	CEN_INFO_ID-->主键	49862
ES_EMP_INFO	1605350	UNIQ_NO-->主键	1605350

思路：

1、从执行计划中发现最消耗的部分ES_EMP_INFO和ES_EMP_CER

2、因为EMP.UNIQ_NO = CER.UNIQ_NO(+) ，ES_EMP_CER基于ES_EMP_INFO，所以想着把ES_EMP_INFO优化索引扫描

3、从sql中能发现导致ES_EMP_INFO不走索引是not exists部分,因为CS_ORDER.UNIQ_NO num_distinct =583936 占ES_EMP_INFO整表的30% ，走索引回表性能低于全扫，所以选择全扫

4、我们去sql 仔细观察会发现 exist and not exists 2部分都是且只有EMP.UNIQ_NO做条件，而且2着关系是and [交集]，所以not exists 可以放在 exists 里面，这样还解决不了问题

5、需要将最根本的EMP.UNIQ_NO与CS_ORDER.UNIQ_NO关联字段更改，又因为EMP.UNIQ_NO = LATION.UNIQ_NO，所以可以将与CS_ORDER.UNIQ_NO的关联字段EMP.UNIQ_NO更改为LATION.UNIQ_NO

6、并且加CO.UNIQ_NO is not null /*不加UNIQ_NO is not null不会走索引,∵索引不记录空值*/... 【个人喜欢将 exists 改成in ，not exists改成not in 避免有时选择filter】

详情见如上优化后的sql

如下是我的推测测试：

验证推测①

oracle衡量得到rownum=n 和不加(rownum=n)性能哪个要好 [不考虑数据匹配情况，只考虑范围的性能]

如下测试是说【rownum <= 90，cost =23448 】<【 rownum <= 90，cost =23551 】

可以看出该sql在rownum<=91开始，cbo觉得索引回表没有全扫快了...

rownum<=90 执行计划和时间

SQL> set autot traceonly

SQL> select *

2    from (SELECT EMP.UNIQ_NO,

3                 EMP.EMP_NAME,

4                 EMP.SEX,

5                 CER.CARD_TYPE,

6                 CER.CARD_NUM,

7                 EMP.IS_BEIJING ENTRYTYPE,

8                 EMP.EMP_TYPE,

9                 EMP.HOUSEHOLD_ADDR

10            FROM ES_EMP_INFO EMP, ES_EMP_CER CER

11           WHERE 1 = 1

12             AND EMP.UNIQ_NO = CER.UNIQ_NO(+)

13             AND CER.IS_APP_UNIQ_NO(+) = 1

14             AND CER.IS_VALID(+) = 1

15             AND EXISTS

16           (SELECT 1

17                    FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD

18                   WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID

19                     AND LATION.IS_VALID = 1

20                     AND HOLD.IS_VALID = 1

21                     AND HOLD.DOOR_STATE = 1

22                     AND LATION.UNIQ_NO = EMP.UNIQ_NO)

23             AND NOT EXISTS

24           (SELECT 1 FROM CS_ORDER CO WHERE CO.UNIQ_NO = EMP.UNIQ_NO)

25           ORDER BY EMP.UNIQ_NO)

26   where rownum <= 90;

90 rows selected.

Elapsed: 00:10:56.77

Execution Plan

----------------------------------------------------------

Plan hash value: 1623837912

---------------------------------------------------------------------------------------------------------

| Id | Operation                        | Name                 | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                 |                      |    90 | 42750 | 23448   (3)| 00:04:42 |

|* 1 | COUNT STOPKEY                   |                      |       |       |            | |

|   2 |   VIEW                           |                      |    93 | 44175 | 23448   (3)| 00:04:42 |

|   3 |    NESTED LOOPS OUTER            |                      |    93 | 6975 | 23448   (3)| 00:04:42 |

|   4 |     NESTED LOOPS SEMI            |                      |    81 | 3321 | 23124   (3)| 00:04:38 |

|   5 |      NESTED LOOPS ANTI           |                      |    81 | 2835 |   222   (0)| 00:00:03 |

|   6 |       TABLE ACCESS BY INDEX ROWID| ES_EMP_INFO          | 1605K|    44M|    93   (0)| 00:00:02 |

|   7 |        INDEX FULL SCAN           | PK_ES_EMP_INFO       |   128 |       |     3   (0)| 00:00:01 |

|* 8 |       INDEX RANGE SCAN           | IDX_CS_ORDER_UNIQ_NO |   225K| 1323K|     2   (0)| 00:00:01 |

|* 9 |      VIEW                        | VW_SQ_1              | 8310 | 49860 |   283   (3)| 00:00:04 |

|* 10 |       HASH JOIN                  |                      | 8310 |   202K|   283   (3)| 00:00:04 |

|* 11 |        TABLE ACCESS FULL         | ES_EMP_HOUSEHOLD     | 8310 | 91410 |   231   (3)| 00:00:03 |

|* 12 |        TABLE ACCESS FULL         | ES_EMP_HDRELATION    | 24931 |   340K|    51   (4)| 00:00:01 |

|* 13 |     TABLE ACCESS BY INDEX ROWID | ES_EMP_CER           |     1 |    34 |     4   (0)| 00:00:01 |

|* 14 |      INDEX RANGE SCAN            | RE_EMP_CER_FK        |     2 |       |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<=90)

   8 - access("CO"."UNIQ_NO"="EMP"."UNIQ_NO")

   9 - filter("ITEM_1"="EMP"."UNIQ_NO")

10 - access("LATION"."CEN_INFO_ID"="HOLD"."CEN_INFO_ID")

11 - filter("HOLD"."DOOR_STATE"=1 AND "HOLD"."IS_VALID"=1)

12 - filter("LATION"."IS_VALID"=1)

13 - filter("CER"."IS_APP_UNIQ_NO"(+)=1 AND "CER"."IS_VALID"(+)=1)

14 - access("EMP"."UNIQ_NO"="CER"."UNIQ_NO"(+))

Statistics

----------------------------------------------------------

          0 recursive calls

      63255 db block gets

   35969615 consistent gets

         41 physical reads

          0 redo size

       5404 bytes sent via SQL*Net to client

        579 bytes received via SQL*Net from client

          7 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

         90 rows processed

rownum<=91 执行计划和时间

SQL> select *

2    from (SELECT EMP.UNIQ_NO,

3                 EMP.EMP_NAME,

4                 EMP.SEX,

5                 CER.CARD_TYPE,

6                 CER.CARD_NUM,

7                 EMP.IS_BEIJING ENTRYTYPE,

8                 EMP.EMP_TYPE,

9                 EMP.HOUSEHOLD_ADDR

10            FROM ES_EMP_INFO EMP, ES_EMP_CER CER

11           WHERE 1 = 1

12             AND EMP.UNIQ_NO = CER.UNIQ_NO(+)

13             AND CER.IS_APP_UNIQ_NO(+) = 1

14             AND CER.IS_VALID(+) = 1

15             AND EXISTS

16           (SELECT 1

17                    FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD

18                   WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID

19                     AND LATION.IS_VALID = 1

20                     AND HOLD.IS_VALID = 1

21                     AND HOLD.DOOR_STATE = 1

22                     AND LATION.UNIQ_NO = EMP.UNIQ_NO)

23             AND NOT EXISTS

24           (SELECT 1 FROM CS_ORDER CO WHERE CO.UNIQ_NO = EMP.UNIQ_NO)

25           ORDER BY EMP.UNIQ_NO)

26   where rownum <= 91;

91 rows selected.

Elapsed: 00:00:04.69

Execution Plan

----------------------------------------------------------

Plan hash value: 946057453

--------------------------------------------------------------------------------------------------

| Id | Operation                 | Name                 | Rows | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |                      |    91 | 43225 | 23551   (3)| 00:04:43 |

|* 1 | COUNT STOPKEY            |                      |       |       |            |          |

|   2 |   VIEW                    |                      | 5263 | 2441K| 23551   (3)| 00:04:43 |

|* 3 |    SORT ORDER BY STOPKEY |                      | 5263 |   385K| 23551   (3)| 00:04:43 |

|* 4 |     HASH JOIN ANTI        |                      | 5263 |   385K| 23549   (3)| 00:04:43 |

|* 5 |      HASH JOIN OUTER      |                      | 8310 |   559K| 23140   (3)| 00:04:38 |

|* 6 |       HASH JOIN RIGHT SEMI|                      | 8310 |   284K| 14413   (3)| 00:02:53 |

|   7 |        VIEW               | VW_SQ_1              | 8310 | 49860 |   283   (3)| 00:00:04 |

|* 8 |         HASH JOIN         |                      | 8310 |   202K|   283   (3)| 00:00:04 |

|* 9 |          TABLE ACCESS FULL| ES_EMP_HOUSEHOLD     | 8310 | 91410 |   231   (3)| 00:00:03 |

|* 10 |          TABLE ACCESS FULL| ES_EMP_HDRELATION    | 24931 |   340K|    51   (4)| 00:00:01 |

| 11 |        TABLE ACCESS FULL | ES_EMP_INFO          | 1605K|    44M| 14110   (2)| 00:02:50 |

|* 12 |       TABLE ACCESS FULL   | ES_EMP_CER           | 1046K|    33M| 8714   (3)| 00:01:45 |

| 13 |      INDEX FAST FULL SCAN | IDX_CS_ORDER_UNIQ_NO |   621K| 3639K|   401   (3)| 00:00:05 |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<=91)

   3 - filter(ROWNUM<=91)

   4 - access("CO"."UNIQ_NO"="EMP"."UNIQ_NO")

   5 - access("EMP"."UNIQ_NO"="CER"."UNIQ_NO"(+))

   6 - access("ITEM_1"="EMP"."UNIQ_NO")

   8 - access("LATION"."CEN_INFO_ID"="HOLD"."CEN_INFO_ID")

   9 - filter("HOLD"."DOOR_STATE"=1 AND "HOLD"."IS_VALID"=1)

10 - filter("LATION"."IS_VALID"=1)

12 - filter("CER"."IS_APP_UNIQ_NO"(+)=1 AND "CER"."IS_VALID"(+)=1)

Statistics

----------------------------------------------------------

          1 recursive calls

          6 db block gets

      85816 consistent gets

       4465 physical reads

          0 redo size

       5582 bytes sent via SQL*Net to client

        590 bytes received via SQL*Net from client

          8 SQL*Net roundtrips to/from client

          1 sorts (memory)

          0 sorts (disk)

         91 rows processed

SQL>

验证推测②

如果n个数据与内层关联都能匹配那对性能有很大帮助①，但如果每次取出n行数据与内层关联没有得到正确数据，将继续循环....直到有数据也就是直到满足rownum=n 行结束...②

上文有验证小②，没有筛选到数据会一直循环知道满足rownum<=n , ES_EMP_INFO.UNIQ_NO最小值=0,实际UNIQ_NO筛选后最小值=4033; 且执行时间65 s 符合了小②的推测..

所以如下实验下查询直接满足对性能的影响【也是rownum的好处】

--如下sql都是基于ES_EMP_INFO表且都属于半连接，所以找到主键最小值就满足sql 要求结果...所以推出带rownum<=1的要比不带rownum<=1要快..

-- 往下走看看吧

用rownum<=n 执行计划和时间

SQL> set autot on

SQL> select UNIQ_NO

2    from (SELECT EMP.UNIQ_NO

3            FROM ES_EMP_INFO EMP, ES_EMP_CER CER

4           WHERE 1 = 1

5             AND EMP.UNIQ_NO = CER.UNIQ_NO(+)

6             AND CER.IS_APP_UNIQ_NO(+) = 1

7             AND CER.IS_VALID(+) = 1

8           order by UNIQ_NO)

9   where rownum <= 1;

   UNIQ_NO

----------

         0

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 3105004118

-------------------------------------------------------------------------------------------------

| Id | Operation                      | Name           | Rows | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                |     1 |    13 |     7   (0)| 00:00:01 |

|* 1 | COUNT STOPKEY                 |                |       |       |            |          |

|   2 |   VIEW                         |                |     1 |    13 |     7   (0)| 00:00:01 |

|   3 |    NESTED LOOPS OUTER          |                |     1 |    18 |     7   (0)| 00:00:01 |

|   4 |     INDEX FULL SCAN            | PK_ES_EMP_INFO | 1605K| 9406K|     3   (0)| 00:00:01 |

|* 5 |     TABLE ACCESS BY INDEX ROWID| ES_EMP_CER     |     1 |    12 |     4   (0)| 00:00:01 |

|* 6 |      INDEX RANGE SCAN          | RE_EMP_CER_FK |     2 |       |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<=1)

   5 - filter("CER"."IS_APP_UNIQ_NO"(+)=1 AND "CER"."IS_VALID"(+)=1)

   6 - access("EMP"."UNIQ_NO"="CER"."UNIQ_NO"(+))

Statistics

----------------------------------------------------------

          1 recursive calls

          0 db block gets

          6 consistent gets

          0 physical reads

          0 redo size

        524 bytes sent via SQL*Net to client

        524 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

不用rownum<=n 执行计划和时间

SQL> select UNIQ_NO

2    from (SELECT EMP.UNIQ_NO,rownum rn

3            FROM ES_EMP_INFO EMP, ES_EMP_CER CER

4           WHERE 1 = 1

5             AND EMP.UNIQ_NO = CER.UNIQ_NO(+)

6             AND CER.IS_APP_UNIQ_NO(+) = 1

7             AND CER.IS_VALID(+) = 1

8           order by UNIQ_NO)

9   where rn <= 1;

   UNIQ_NO

----------

         0

Elapsed: 00:00:02.94

Execution Plan

----------------------------------------------------------

Plan hash value: 1545245311

------------------------------------------------------------------------------------------------

| Id | Operation             | Name           | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                | 1605K|    39M|       | 17092   (3)| 00:03:26 |

|* 1 | VIEW                 |                | 1605K|    39M|       | 17092   (3)| 00:03:26 |

|   2 |   COUNT               |                |       |       |       |            |          |

|   3 |    MERGE JOIN OUTER   |                | 1605K|    27M|       | 17092   (3)| 00:03:26 |

|   4 |     INDEX FULL SCAN   | PK_ES_EMP_INFO | 1605K| 9406K|       | 3516   (2)| 00:00:43 |

|* 5 |     SORT JOIN         |                | 1046K|    11M|    48M| 13577   (3)| 00:02:43 |

|* 6 |      TABLE ACCESS FULL| ES_EMP_CER     | 1046K|    11M|       | 8714   (3)| 00:01:45 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"<=1)

   5 - access("EMP"."UNIQ_NO"="CER"."UNIQ_NO"(+))

       filter("EMP"."UNIQ_NO"="CER"."UNIQ_NO"(+))

   6 - filter("CER"."IS_APP_UNIQ_NO"(+)=1 AND "CER"."IS_VALID"(+)=1)

Statistics

----------------------------------------------------------

          1 recursive calls

          1 db block gets

      34723 consistent gets

       1687 physical reads

          0 redo size

        524 bytes sent via SQL*Net to client

        524 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          1 sorts (memory)

          0 sorts (disk)

          1 rows processed

SQL>

关于rownum其他有意思的博客【不用rownum则会报“ora-01722：无效数字” http://blog.itpub.net/12932950/viewspace-662933/ 】

【源于本人笔记】若有书写错误，表达错误，请指正...

此条目发表在 SQL、SQL优化篇分类目录。将固定连接加入收藏夹。

Oracle rownum 分页引起的效率问题及优化思路

原文：http://blog.itpub.net/28602568/viewspace-1366015/

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)