Oracle rownum 分頁引起的效率問題及優化思路

kingsql發表於2014-12-19
Oracle優化

標題： Oracle rownum 分頁引起的效率問題及優化思路

作者：ｌōττéｒｙ©版權所有[文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任.]

前言：
   前不久通過 查詢每天執行慢的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--&gt普通索引  583936
ES_EMP_CER 2588497 UNIQ_NO--&gt普通索引  1611008
ES_EMP_HDRELATION 49862 UNIQ_NO--&gt主鍵  30282
ES_EMP_HDRELATION 49862 CEN_INFO_ID --&gtES_EMP_HDRELATION外來鍵  49862
ES_EMP_HOUSEHOLD 49862 CEN_INFO_ID--&gt主鍵  49862
ES_EMP_INFO 1605350 UNIQ_NO--&gt主鍵  1605350




詭異之處：
  單獨查詢內層的sql 2s ， 加一層where rownum <=1 執行65s；   --&gt 理論上會覺得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 的執行計劃和執行時間’來作為參考
  如下：
  執行計劃：
     ---------------------------------------------------------------------------------------------------
     | Id  | Operation                  | Name                  | Rows   | Bytes | Cost (%CPU )| Time     |
     ---------------------------------------------------------------------------------------------------
     |   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--&gt普通索引  583936
ES_EMP_CER 2588497 UNIQ_NO--&gt普通索引  1611008
ES_EMP_HDRELATION 49862 UNIQ_NO--&gt主鍵  30282
ES_EMP_HDRELATION 49862 CEN_INFO_ID--&gtES_EMP_HDRELATION外來鍵  49862
ES_EMP_HOUSEHOLD 49862 CEN_INFO_ID--&gt主鍵  49862
ES_EMP_INFO 1605350 UNIQ_NO--&gt主鍵  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>  
 
 

  【源於本人筆記】 若有書寫錯誤，表達錯誤，請指正...
此條目發表在 SQL、SQL優化篇 分類目錄。將固定連線加入收藏夾。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1372952/，如需轉載，請註明出處，否則將追究法律責任。

相關文章