不走索引場景的一次分析優化

一般一個SQL查詢資料量很大，且優化餘地不大時，我們必然想開啟并行，用并行的方式提高資料的查詢速度，然後不是任何情況下開啟并行都可以達到最佳運作效果，

有時原本使用索引的執行計劃，因為使用并行反而該走全表掃描，是以必須通過hint方式引導優化器采取正确的執行計劃，對于如下SQL

SELECT

T1.RPO_NO

,T_LGIN.LGIN_DT

,T_LGIN.USER_ID

,T_LGIN.USER_IP

,T_LGIN.CLNT_IP

,T_LGIN.MAC_ADDR

,T_LGIN.MENU_SYS_CD

,ROW_NUMBER() OVER(PARTITION BY T1.RPO_NO ORDER BY T_LGIN.LGIN_DT DESC) RNK

FROM MCS_HQ_READ.UP_RPO_TRACE_0602 T1

,MCS_HQ.HI_USER_LGIN T_LGIN

WHERE T_LGIN.USER_ID = T1.REQ_ID

AND T_LGIN.LGIN_DT >= TRUNC(T1.REQ_DT)

AND T_LGIN.LGIN_DT <= T1.REQ_DT;

不采取任何方式人工幹預，優化器生成的執行計劃将按索引查詢表HI_USER_LGIN，但因HI_USER_LGIN表内資料量很大，查詢非常消耗資源，是以開啟并行，提高查詢速度。

開始引入并行的hint如下所示

SQL_ID 7f2gdrbqzv7d8, child number 0

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

SELECT /*+ PARALLEL(T_LGIN,8) PARALLEL(T1,8) ALL_ROWS */

T1.RPO_NO /*-testNL0000001*/

,T_LGIN.LGIN_DT ,T_LGIN.USER_ID

,T_LGIN.USER_IP ,T_LGIN.CLNT_IP

,T_LGIN.MAC_ADDR ,T_LGIN.MENU_SYS_CD

,ROW_NUMBER() OVER(PARTITION BY T1.RPO_NO ORDER BY

T_LGIN.LGIN_DT DESC) RNK FROM

MCS_HQ_READ.UP_RPO_TRACE_0602 T1

,MCS_HQ.HI_USER_LGIN T_LGIN WHERE T_LGIN.USER_ID

= T1.REQ_ID AND T_LGIN.LGIN_DT >=

TRUNC(T1.REQ_DT) AND T_LGIN.LGIN_DT <= T1.REQ_DT

Plan hash value: 3061441924

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

| 0 | SELECT STATEMENT | | |

| 1 | PX COORDINATOR | | |

| 2 | PX SEND QC (RANDOM) | :TQ10002 | 307K|

| 3 | WINDOW SORT | | 307K|

| 4 | PX RECEIVE | | 307K|

| 5 | PX SEND HASH | :TQ10001 | 307K|

|* 6 | HASH JOIN | | 307K|

| 7 | PX RECEIVE | | 396K|

| 8 | PX SEND BROADCAST | :TQ10000 | 396K|

| 9 | PX BLOCK ITERATOR | | 396K|

|* 10 | TABLE ACCESS FULL| UP_RPO_TRACE_0602 | 396K|

| 11 | PX BLOCK ITERATOR | | 35M|

|* 12 | TABLE ACCESS FULL | HI_USER_LGIN | 35M|

Predicate Information (identified by operation id):

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

6 - access("T_LGIN"."USER_ID"="T1"."REQ_ID")

filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT"

)) AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))

10 - access(:Z>=:Z AND :Z<=:Z)

12 - access(:Z>=:Z AND :Z<=:Z)

Note

-----

- dynamic sampling used for this statement (level=5)

- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system level

從執行計劃看，說明優化器并沒有按之前的計劃采用索引方式掃描HI_USER_LOGIN，而是直接采用并行全表掃描的方式，說明并行不是在原來計劃的基礎上添加并行，但全表掃描如此大量的資料，

會降低查詢速度，是以這裡嘗試人工幹預執行計劃，令其查詢HI_USER_LOGIN時走索引，檢視該表上對應的連接配接字段上有索引X_HI_USER_LGIN_2(LGIN_DT,USER_ID),是以引導優化器，

令連接配接的連個表采用嵌套循環連接配接方式，時掃描大表時，優化器能使用大表上的索引查詢連接配接字段的值，修改hint後的執行計劃

SQL_ID g1thg0qgnk745, child number 0

SELECT /*+ LEADING(T1) USE_NL(T_LGIN) PARALLEL(T_LGIN,8)

PARALLEL(T1,8) ALL_ROWS */ T1.RPO_NO

/*-testNL*/ ,T_LGIN.LGIN_DT

,T_LGIN.USER_ID ,T_LGIN.USER_IP

,T_LGIN.CLNT_IP ,T_LGIN.MAC_ADDR

,T_LGIN.MENU_SYS_CD ,ROW_NUMBER()

OVER(PARTITION BY T1.RPO_NO ORDER BY T_LGIN.LGIN_DT DESC) RNK

FROM MCS_HQ_READ.UP_RPO_TRACE_0602 T1

,MCS_HQ.HI_USER_LGIN T_LGIN WHERE

T_LGIN.USER_ID = T1.REQ_ID AND T_LGIN.LGIN_DT

>= TRUNC(T1.REQ_DT) AND T_LGIN.LGIN_DT <=

T1.REQ_DT

Plan hash value: 3231175795

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

| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 | 0 |

| 1 | PX COORDINATOR | | 0 | | 0 |00:00:00.01 | 0 | 0 |

| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 307K| 0 |00:00:00.01 | 0 | 0 |

| 3 | WINDOW SORT | | 0 | 307K| 0 |00:00:00.01 | 0 | 0 |

| 4 | PX RECEIVE | | 0 | | 0 |00:00:00.01 | 0 | 0 |

| 5 | PX SEND HASH | :TQ10000 | 0 | | 0 |00:00:00.01 | 0 | 0 |

| 6 | NESTED LOOPS | | 1 | | 185K|00:01:06.73 | 720K| 25072 |

| 7 | NESTED LOOPS | | 1 | 307K| 185K|00:00:44.02 | 538K| 6995 |

| 8 | PX BLOCK ITERATOR | | 1 | | 67750 |00:00:01.00 | 2338 | 2129 |

|* 9 | TABLE ACCESS FULL | UP_RPO_TRACE_0602 | 15 | 396K| 67750 |00:00:00.97 | 2338 | 2129 |

|* 10 | INDEX RANGE SCAN | X_HI_USER_LGIN_2 | 67750 | 16 | 185K|00:00:42.87 | 536K| 4866 |

| 11 | TABLE ACCESS BY INDEX ROWID| HI_USER_LGIN | 185K| 1 | 185K|00:00:22.49 | 181K| 18077 |

9 - access(:Z>=:Z AND :Z<=:Z)

10 - access("T_LGIN"."USER_ID"="T1"."REQ_ID" AND "T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT"))

AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT")

filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))

顯然，使用并行後，需要認為使用hint幹預執行計劃，使其使用合理的索引，提高查詢速度

SQL_ID gyn8zxr8uchgs, child number 0

SELECT /*+ LEADING(T1) USE_HASH(T_LGIN) PARALLEL(T_LGIN,8)

/*-test01*/ ,T_LGIN.LGIN_DT

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

| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 | 0 |

| 1 | PX COORDINATOR | | 0 | | 0 |00:00:00.01 | 0 | 0 |

| 2 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 307K| 0 |00:00:00.01 | 0 | 0 |

| 3 | WINDOW SORT | | 0 | 307K| 0 |00:00:00.01 | 0 | 0 |

| 4 | PX RECEIVE | | 0 | 307K| 0 |00:00:00.01 | 0 | 0 |

| 5 | PX SEND HASH | :TQ10001 | 0 | 307K| 0 |00:00:00.01 | 0 | 0 |

|* 6 | HASH JOIN | | 1 | 307K| 221K|00:00:46.56 | 66137 | 66027 |

| 7 | PX RECEIVE | | 1 | 396K| 513K|00:00:00.56 | 0 | 0 |

| 8 | PX SEND BROADCAST | :TQ10000 | 0 | 396K| 0 |00:00:00.01 | 0 | 0 |

| 9 | PX BLOCK ITERATOR | | 0 | 396K| 0 |00:00:00.01 | 0 | 0 |

|* 10 | TABLE ACCESS FULL| UP_RPO_TRACE_0602 | 0 | 396K| 0 |00:00:00.01 | 0 | 0 |

| 11 | PX BLOCK ITERATOR | | 1 | 35M| 4718K|00:00:06.03 | 66137 | 66027 |

|* 12 | TABLE ACCESS FULL | HI_USER_LGIN | 22 | 35M| 4718K|00:00:04.62 | 66137 | 66027 |

filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND

"T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))

SQL_ID db4n20k7nt2bh, child number 0

SELECT /*+ LEADING(T1) USE_NL(T_LGIN) */

T1.RPO_NO /*-testNL1*/ ,T_LGIN.LGIN_DT

,T_LGIN.USER_ID ,T_LGIN.USER_IP

,T_LGIN.CLNT_IP ,T_LGIN.MAC_ADDR

,T_LGIN.MENU_SYS_CD ,ROW_NUMBER()

Plan hash value: 1850855573

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

| 0 | SELECT STATEMENT | | 1 | | 100 |00:03:53.23 | 5480K| 16439 | 21677 |

| 1 | WINDOW SORT | | 1 | 307K| 100 |00:03:53.23 | 5480K| 16439 | 21677 |

| 2 | NESTED LOOPS | | 1 | | 1396K|00:03:50.52 | 5480K| 16397 | 0 |

| 3 | NESTED LOOPS | | 1 | 307K| 1396K|00:03:42.31 | 4111K| 16379 | 0 |

| 4 | TABLE ACCESS FULL | UP_RPO_TRACE_0602 | 1 | 396K| 513K|00:00:05.64 | 16479 | 16378 | 0 |

|* 5 | INDEX RANGE SCAN | X_HI_USER_LGIN_2 | 513K| 1 | 1396K|00:03:35.82 | 4095K| 1 | 0 |

| 6 | TABLE ACCESS BY INDEX ROWID| HI_USER_LGIN | 1396K| 1 | 1396K|00:00:06.86 | 1368K| 18 | 0 |

5 - access("T_LGIN"."USER_ID"="T1"."REQ_ID" AND "T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND

"T_LGIN"."LGIN_DT"<="T1"."REQ_DT")

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

不走索引場景的一次分析優化

繼續閱讀

Oracle索引内部結構研究

MySQL和Lucene索引對比分析1. MySQL索引實作2. Lucene索引實作3. MySQL與Lucence對比參考：

一文搞定 MySQL 索引

MySQL（第五篇）—資料的完整性限制（2）（索引、引用完整性）資料的完整性限制（2）三、索引四、引用完整性

【MySQL索引】MySQL索引分類，90%的開發都不知道一、根據底層資料結構劃分二、根據索引字段個數劃分三、根據是否是在主鍵上建立的索引進行劃分四、根據資料與索引的存儲關聯性劃分五、其他分類六、總結

JS--------for循環删除數組元素

Python中字元串常見操作總結

Lucence的基本原理

sql執行計劃 explain 以及結合索引優化sql

MySQL - order by排序優化

2021-08-08 mysql索引

SQLServer 提升查詢速度

詳解SQL中幾種常用的表連接配接方式

oracle 中不使用已有的索引解決辦法

對first_name建立唯一索引uniq_idx_firstname問題描述Sql語句

記一次因MySQL編碼問題導緻的慢查詢排查