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; 大概情況:
|
詭異之處:
單獨查詢內層的sql 2s , 加一層where rownum <=1 執行65s; --> 理論上會覺得where rownum是內層查詢完之後再過濾的....到底是什麼引起的呢?
那我們分別看下 帶不帶rownum 的執行計劃 和執行時間
帶rownum <= 1 的執行計劃和執行時間
SQL> SET AUTOT on -- /*TRACEONLY */SQL> set line 100SQL> set timing onSQL> 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_ADDR10 FROM ES_EMP_INFO EMP, ES_EMP_CER CER11 WHERE 1 = 112 AND EMP.UNIQ_NO = CER.UNIQ_NO(+)13 AND CER.IS_APP_UNIQ_NO(+) = 114 AND CER.IS_VALID(+) = 115 AND EXISTS16 ( SELECT 117 FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD18 WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID19 AND LATION.IS_VALID = 120 AND HOLD.IS_VALID = 121 AND HOLD.DOOR_STATE = 122 AND LATION.UNIQ_NO = EMP.UNIQ_NO)23 AND NOT EXISTS24 ( 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 calls6223 db block gets3540228 consistent gets0 physical reads0 redo size235 bytes sent via SQL*Net to client251 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processedSQL>
不帶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 CER10 WHERE 1 = 111 AND EMP.UNIQ_NO = CER.UNIQ_NO(+)12 AND CER.IS_APP_UNIQ_NO(+) = 113 AND CER.IS_VALID(+) = 114 AND EXISTS (SELECT 115 FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD16 WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID17 AND LATION.IS_VALID = 118 AND HOLD.IS_VALID = 119 AND HOLD.DOOR_STATE = 120 AND LATION.UNIQ_NO = EMP.UNIQ_NO)21 AND NOT EXISTS22 (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.83Execution 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 calls7 db block gets37418 consistent gets0 physical reads0 redo size41179 bytes sent via SQL*Net to client2130 bytes received via SQL*Net from client148 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)2202 rows processed 如下是1條資料的測試,和該sql寓意相同,且rownum rn 可以理解一個欄位...
SQL> select UNIQ_NO2 from (SELECT c.*, rownum rn3 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_ADDR11 FROM ES_EMP_INFO EMP, ES_EMP_CER CER12 WHERE 1 = 113 AND EMP.UNIQ_NO = CER.UNIQ_NO(+)14 AND CER.IS_APP_UNIQ_NO(+) = 115 AND CER.IS_VALID(+) = 116 AND EXISTS17 (SELECT 118 FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD19 WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID20 AND LATION.IS_VALID = 121 AND HOLD.IS_VALID = 122 AND HOLD.DOOR_STATE = 123 AND LATION.UNIQ_NO = EMP.UNIQ_NO)24 AND NOT EXISTS25 (SELECT 126 FROM CS_ORDER CO27 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 calls6 db block gets85800 consistent gets4462 physical reads0 redo size235 bytes sent via SQL*Net to client251 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)1 rows processedSQL>
總結:
可以看出 加了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_NO2 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_ADDR10 FROM ES_EMP_INFO EMP, ES_EMP_CER CER11 WHERE 1 = 112 AND EMP.UNIQ_NO = CER.UNIQ_NO(+)13 AND CER.IS_APP_UNIQ_NO(+) = 114 AND CER.IS_VALID(+) = 115 AND EMP.UNIQ_NO in16 ( SELECT LATION.UNIQ_NO17 FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD18 WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID19 AND LATION.IS_VALID = 120 AND HOLD.IS_VALID = 121 AND HOLD.DOOR_STATE = 122 and LATION.UNIQ_NO not in23 ( SELECT CO.UNIQ_NO24 FROM CS_ORDER CO25 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 calls4 db block gets18622 consistent gets24 physical reads104 redo size235 bytes sent via SQL*Net to client251 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 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-->普通索引 | 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 traceonlySQL> 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_ADDR10 FROM ES_EMP_INFO EMP, ES_EMP_CER CER11 WHERE 1 = 112 AND EMP.UNIQ_NO = CER.UNIQ_NO(+)13 AND CER.IS_APP_UNIQ_NO(+) = 114 AND CER.IS_VALID(+) = 115 AND EXISTS16 (SELECT 117 FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD18 WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID19 AND LATION.IS_VALID = 120 AND HOLD.IS_VALID = 121 AND HOLD.DOOR_STATE = 122 AND LATION.UNIQ_NO = EMP.UNIQ_NO)23 AND NOT EXISTS24 (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.77Execution 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 calls63255 db block gets35969615 consistent gets41 physical reads0 redo size5404 bytes sent via SQL*Net to client579 bytes received via SQL*Net from client7 SQL*Net roundtrips to/from client0 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_ADDR10 FROM ES_EMP_INFO EMP, ES_EMP_CER CER11 WHERE 1 = 112 AND EMP.UNIQ_NO = CER.UNIQ_NO(+)13 AND CER.IS_APP_UNIQ_NO(+) = 114 AND CER.IS_VALID(+) = 115 AND EXISTS16 (SELECT 117 FROM ES_EMP_HDRELATION LATION, ES_EMP_HOUSEHOLD HOLD18 WHERE LATION.CEN_INFO_ID = HOLD.CEN_INFO_ID19 AND LATION.IS_VALID = 120 AND HOLD.IS_VALID = 121 AND HOLD.DOOR_STATE = 122 AND LATION.UNIQ_NO = EMP.UNIQ_NO)23 AND NOT EXISTS24 (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.69Execution 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 calls6 db block gets85816 consistent gets4465 physical reads0 redo size5582 bytes sent via SQL*Net to client590 bytes received via SQL*Net from client8 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)91 rows processedSQL>
驗證推測②
如果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 onSQL> select UNIQ_NO2 from (SELECT EMP.UNIQ_NO3 FROM ES_EMP_INFO EMP, ES_EMP_CER CER4 WHERE 1 = 15 AND EMP.UNIQ_NO = CER.UNIQ_NO(+)6 AND CER.IS_APP_UNIQ_NO(+) = 17 AND CER.IS_VALID(+) = 18 order by UNIQ_NO)9 where rownum <= 1;UNIQ_NO----------0Elapsed: 00:00:00.01Execution 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 calls0 db block gets6 consistent gets0 physical reads0 redo size524 bytes sent via SQL*Net to client524 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed
不用rownum<=n 執行計劃和時間SQL> select UNIQ_NO2 from (SELECT EMP.UNIQ_NO,rownum rn3 FROM ES_EMP_INFO EMP, ES_EMP_CER CER4 WHERE 1 = 15 AND EMP.UNIQ_NO = CER.UNIQ_NO(+)6 AND CER.IS_APP_UNIQ_NO(+) = 17 AND CER.IS_VALID(+) = 18 order by UNIQ_NO)9 where rn <= 1;UNIQ_NO----------0Elapsed: 00:00:02.94Execution 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 calls1 db block gets34723 consistent gets1687 physical reads0 redo size524 bytes sent via SQL*Net to client524 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)1 rows processedSQL>
【源於本人筆記】 若有書寫錯誤,表達錯誤,請指正...
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1405538/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle rownum 分頁引起的效率問題及優化思路Oracle優化
- Oracle Rownum分頁改寫Oracle
- oracle 使用rownum進行分頁的陷阱Oracle
- oracle 使用rownum進行分頁的陷阱(2)Oracle
- oracle 分頁問題Oracle
- 淺析Oracle(rownum)和Mysql(limit)分頁的區別OracleMySqlMIT
- 關於分頁查詢的最佳化思路
- oracle rownum分頁與顯示記錄小測Oracle
- Oracle分頁查詢中排序與效率問題解決方法詳解Oracle排序
- Oracle中rownum與rowid使用上的問題Oracle
- SQL Server的分頁優化及Row_Number()分頁存在的問題SQLServer優化
- Rownum分頁故障解決一例
- Oracle最佳化之單表分頁最佳化Oracle
- oracle的rownumOracle
- pageHelper分頁外掛導致的查詢慢的問題最佳化
- 由一個C++版本猜數字遊戲引起的效率問題C++遊戲
- Oracle建立索引選擇合適的可選項及效率問題Oracle索引
- 應用部署引起上游服務抖動問題分析及最佳化實踐方案
- Oracle DRM引起的問題解決一例Oracle
- 分頁及查詢引數傳遞問題分享
- Mysql 分頁效率不同的SQLMySql
- 關於 Laravel 分頁的問題?Laravel
- 分頁查詢的排序問題排序
- ibatis分頁問題BAT
- Oracle中的rownumOracle
- Oracle ROWNUMOracle
- Laravel 裡面的 chunk 分塊效率問題Laravel
- 解決oracle中not like效率問題Oracle
- vue + element-ui的分頁問題VueUI
- 分頁查詢的排序問題(二)排序
- 批次分頁查詢問題?
- jdon 的分頁機制的效率低,
- 由於內部連線引起的Oracle RAC效能問題Oracle
- 【 Oracle中rownum的用法 】Oracle
- 系統投產前,Oracle資料庫最佳化思路和9個典型問題Oracle資料庫
- 16個經典面試問題及回答思路面試
- 處理分頁的result型別問題型別
- iOS Swift UICollectionView橫向分頁的問題iOSSwiftUIView