改寫不走索引的SQL

如下SQL為保證所有資料都出自UP_LOAD_SERL10表(後面將以別名T3稱呼此表，其他表也已別名稱呼)
SELECT
       T1.SERL_NO
      ,T1.SI_YMD
      ,T1.ST_YMD
      ,T1.ST2_YMD
      ,T1.SO_YMD
      ,T1.ACTU_DT    FOTA_DT
      ,T2.SALE_YMD CHANEL_SO
      ,T2.REGI_DT   CHANEL_SO_REGI_DT
FROM MCS_BI.RW_BI_FT_LT T1
    ,MCS_HQ.HI_SALE_CHNL T2
,MCS_HQ_READ.UP_LOAD_SERL10 T3
WHERE T1.SERL_NO = T2.SERL_NO(+)
AND   T3.SERL_NO = T1.SERL_NO(+);
此SQL執行足有5分鐘，T1和T2表資料非常大足都超過千萬行，而T3資料很小隻有60行，為實現需要得出的結果，三個表在一起必須使用外連線，執行計劃如下
Plan hash value: 3150097070

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |      1 |        |     63 |00:01:35.15 |    1173K|   1173K|
|   1 | NESTED LOOPS OUTER                 |                   |      1 |     63 |     63 |00:01:35.15 |    1173K|   1173K|
|* 2 |   HASH JOIN OUTER                   |                   |      1 |     63 |     63 |00:01:34.96 |    1173K|   1173K|
|   3 |    TABLE ACCESS FULL                | UP_LOAD_SERL10    |      1 |     63 |     63 |00:00:00.01 |       7 |      0 |
|   4 |    PARTITION RANGE ALL              |                   |      1 |     38M|     38M|00:01:13.83 |    1173K|   1173K|
|   5 |     TABLE ACCESS FULL               | RW_BI_FT_LT       |    122 |     38M|     38M|00:01:03.25 |    1173K|   1173K|
|   6 |   TABLE ACCESS BY GLOBAL INDEX ROWID| HI_SALE_CHNL      |     63 |      1 |     63 |00:00:00.19 |     191 |     24 |
|* 7 |    INDEX UNIQUE SCAN                | UX_HI_SALE_CHNL_1 |     63 |      1 |     63 |00:00:00.19 |     128 |     24 |
----------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T3"."SERL_NO"="T1"."SERL_NO")
   7 - access("T1"."SERL_NO"="T2"."SERL_NO")

Note
-----
   - dynamic sampling used for this statement (level=2)
由於是三個表做外連線，兩個連線條件必須同時滿足，且T3表又是最小的，優化器選擇T1表和T3表先做HASH JOIN OUTER，然後與T1做NEST LOOP OUTER，從計劃中可以看出T3和T1做外連線時，T1，T3表的SERL_NO上都沒有索引，所以對應都是全表掃描，而T1和T2連線時，T2的SERL_NO上有索引，所以先做了INDEX UNIQUE SCAN，然後通過ROWID回表掃描。這裡T1表的返回行數巨大，全表掃描必然很慢，因此應該想辦法讓T3和T1表連線時，T1的欄位能走索引，檢視T1表發現上面有主鍵索引 PK_INDEX 且索引包括兩個欄位 FOTA_SO_YMD, SERL_NO，因此優化器並沒有使用到此索引，於是選擇優化方式可以改寫SQL讓優化器便於查詢表上的索引並能使用，對SQl採用如下改寫

WITH T_MAST AS (
SELECT T1.SERL_NO
      ,T1.SI_YMD
      ,T1.ST_YMD
      ,T1.ST2_YMD
      ,T1.SO_YMD
      ,T1.ACTU_DT    FOTA_DT
      ,T2.SALE_YMD CHANEL_SO
      ,T2.REGI_DT   CHANEL_SO_REGI_DT
FROM MCS_BI.RW_BI_FT_LT T1
    ,MCS_HQ.HI_SALE_CHNL T2
WHERE T1.SERL_NO = T2.SERL_NO(+)
)
SELECT T_MAST.*
FROM T_MAST,
     MCS_HQ_READ.UP_LOAD_SERL10 T2
WHERE    T_MAST.SERL_NO(+) = T2.SERL_NO;

對應執行計劃
Plan hash value: 2281879921

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                             | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |      1 |        |     63 |00:00:23.39 |   96374 |   2705 |
|   1 | NESTED LOOPS OUTER                   |                   |      1 |     63 |     63 |00:00:23.39 |   96374 |   2705 |
|   2 |   TABLE ACCESS FULL                   | UP_LOAD_SERL10    |      1 |     63 |     63 |00:00:00.01 |       7 |      0 |
|   3 |   VIEW PUSHED PREDICATE               |                   |     63 |      1 |     63 |00:00:23.39 |   96367 |   2705 |
|   4 |    NESTED LOOPS OUTER                 |                   |     63 |      1 |     63 |00:00:23.39 |   96367 |   2705 |
|   5 |     PARTITION RANGE ALL               |                   |     63 |      1 |     63 |00:00:23.17 |   96183 |   2679 |
|   6 |      TABLE ACCESS BY LOCAL INDEX ROWID| RW_BI_FT_LT       |   7686 |      1 |     63 |00:00:23.16 |   96183 |   2679 |
|* 7 |       INDEX SKIP SCAN                 | PK_RW_BI_FT_LT    |   7686 |      1 |     63 |00:00:22.93 |   96121 |   2632 |
|   8 |     TABLE ACCESS BY GLOBAL INDEX ROWID| HI_SALE_CHNL      |     63 |      1 |     63 |00:00:00.22 |     184 |     26 |
|* 9 |      INDEX UNIQUE SCAN                | UX_HI_SALE_CHNL_1 |     63 |      1 |     63 |00:00:00.22 |     121 |     26 |
------------------------------------------------------------------------------------------------------------------------------

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

   7 - access("T1"."SERL_NO"="T2"."SERL_NO")
       filter("T1"."SERL_NO"="T2"."SERL_NO")
   9 - access("T2"."SERL_NO"="T2"."SERL_NO")
       filter("T1"."SERL_NO"="T2"."SERL_NO")

Note
-----
   - dynamic sampling used for this statement (level=2)

這樣原T1，T2關聯時就可以使用欄位中SERL_No的索引，避免了多個條件並列時優化器採用全表掃描的情況，於是優化後的SQL執行限制在10s內

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

改寫不走索引的SQL

相關文章