Fetch 1 0.00 0.00 0 10 0 100
------- ------ -------- -------------------- ---------- ---------- ----------
total 3 0.00 0.00 0 10 0 100
将表table_1数据增加到50万条，执行计划与跟踪结果表明，in 与exists的写法一样的效率。
在9i中，in 与exists的写法在执行计划与跟踪结果结果是不一样的，认为
Select * from T1 where x in ( select y fromT2 )
与
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
是很相近的。
而exists的写法
select * from t1 where exists ( select nullfrom t2 where y = x )
具有以下的逻辑过程
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop
这表明总会导致table_1执行全表扫描，并能使用table_2上的索引
It always results in a full scan of T1whereas the first query can make use of an index on T1(x).
如果( select afrom table_1)结果很大，而(select *from table_2很小)，这时候在table_2(a)存在索引的话，使用exists是有效率的写法。
而如果(select * from table_1)是很小的结果集，则采用in是更好的方法。
如果(select *from table_1) 与(select *from table_2)都是很大的结果集合，要看其他的一些因素，如索引等。
半连接还有一种写法
select count(a.b)
from table_2 a, (select distinct a from table_1) b
where a.a=b.a
但是测试结果表明，无论数据呈现怎样的特征，这种写法在效率上都比不上in 或者exists
我们将table_1,table_2的位置颠倒一下，结果就更加清楚了
EXPLAIN PLAN FOR
select
a.a,a.b
from table_2a, (select distinct a from table_1) b
where a.a=b.a
SELECT * FROM TABLE(DBMS_XPLAN.display());
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1001 | 26026 | 509 (2)| 00:00:07 |
| 1 | VIEW | VM_NWVW_1 | 1001 | 26026 | 509 (2)| 00:00:07 |
| 2 | HASH UNIQUE | | 1001 | 27027 | 509 (2)| 00:00:07 |
|* 3 | HASH JOIN | | 1001 | 27027 | 508 (2)| 00:00:07 |
| 4 | INDEX FAST FULL SCAN| IDX_TAB2_A | 1001 | 3003 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL |TABLE_1 | 500K| 11M| 502 (1)| 00:00:07 |
--------------------------------------------------------------------------------------
EXPLAIN PLAN FOR
select
a.a,a.b
from table_2 a
where a.a in (select a from table_1);
SELECT * FROM TABLE(DBMS_XPLAN.display());
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1001 | 20020 | 313 (2)| 00:00:04 |
|* 1 | HASH JOIN SEMI | | 1001 | 20020 | 313 (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL |TABLE_2 | 1001 | 16016 | 4 (0)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IDX_TAB1_A | 500K| 1953K| 306 (1)| 00:00:04 |
------------------------------------------------------------------------------------
)
EXPLAIN PLAN FOR
select
a.a,a.b
from table_2 a
where exists (select null from table_1 b where a.a=b.a);
SELECT * FROM TABLE(DBMS_XPLAN.display());
----------------------------------------------------------------