前言:
前不久通过
查询每天执行慢的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/28602568/viewspace-1366015/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28602568/viewspace-1366015/