| « | July 2025 | » | | 日 | 一 | 二 | 三 | 四 | 五 | 六 | | | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | | | |
|
| 统计 |
blog名称:V-加油
日志总数:52
评论数量:83
留言数量:0
访问次数:250643
建立时间:2005年2月13日 |
| 
|
本站首页 管理页面 写新日志 退出
 [Oracle学习笔记]关于in和exists的效率 |
askTom上总是看到一些以前与大多的观点不尽相同的观点，这次关于in和exists的效率的讨论，实在是有启发。Tom said:be aware of their differences, try them both when tuning, understand conceptually what they do and you'll be able to use them to maximum effect.
这确实是在这里最大的收获，谢谢Tom
下面摘录一些有用的片段：
i verified it and the "rule of thumb" holds true. BIG outer query and SMALL inner query = IN. SMALL outer query and BIG inner query = WHERE EXISTS. Remember -- thats is a RULE OF THUMB and rules of thumb always have infinitely many exceptions to the rule.
the WHERE EXISTS will find the first row faster in general then the IN will -- the IN will get the LAST row (all rows) faster then the where exists。
Well -- I'm not going to use EMP and DEPT as I would have to generate tons of EMP and DEPT data to illustrate my point (feel free to do that if you want to ;)
I'll use BIG and SMALL to make the point. I ran:
rem create table big as select * from all_objects;rem insert /*+ append */ into big select * from big;rem commit;rem insert /*+ append */ into big select * from big;rem commit;rem insert /*+ append */ into big select * from big;rem create index big_idx on big(object_id);remremrem create table small as select * from all_objects where rownum < 100;rem create index small_idx on small(object_id);remrem analyze table big compute statisticsrem for tablerem for all indexesrem for all indexed columnsrem /rem analyze table small compute statisticsrem for tablerem for all indexesrem for all indexed columnsrem /
so, small has 99 rows, big has 133,000+
select count(subobject_name) from big where object_id in ( select object_id from small )
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.01 0.01 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.02 0.02 0 993 0 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.03 0.03 0 993 0 1
Rows Execution Plan------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 792 MERGE JOIN 100 SORT (JOIN) 100 VIEW OF 'VW_NSO_1' 99 SORT (UNIQUE) 792 INDEX GOAL: ANALYZED (FULL SCAN) OF 'SMALL_IDX' (NON-UNIQUE) 891 SORT (JOIN) 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'BIG'
versus:
select count(subobject_name) from big where exists ( select null from small where small.object_id = big.object_id )
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 4.12 4.12 0 135356 15 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 4.12 4.12 0 135356 15 1
Rows Execution Plan------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 792 FILTER 135297 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'BIG' 133504 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'SMALL_IDX' (NON-UNIQUE)
That shows if the outer query is "big" and the inner query is "small", in is generally more efficient then NOT EXISTS.
Now:
select count(subobject_name) from small where object_id in ( select object_id from big )
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.01 0.01 0 0 0 0Execute 2 0.00 0.00 0 0 0 0Fetch 2 0.51 0.82 50 298 22 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 5 0.52 0.83 50 298 22 1
Rows Execution Plan------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 99 MERGE JOIN 16913 SORT (JOIN) 16912 VIEW OF 'VW_NSO_1' 16912 SORT (UNIQUE) 135296 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF 'BIG_IDX' (NON-UNIQUE) 99 SORT (JOIN) 99 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SMALL'
versus:select count(subobject_name) from small where exists ( select null from big where small.object_id = big.object_id )
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.01 0.01 0 204 12 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.01 0.01 0 204 12 1
EGATE) 99 FILTER 100 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SMALL' 99 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'BIG_IDX' (NON-UNIQUE)
shows that is the outer query is "small" and the inner query is "big" -- a WHERE EXISTS can be quite efficient.
|
阅读全文(1968) | 回复(0) | 编辑 | 精华 |
|
