4. in & exist
1) 与in/exists等价的连接查询
2) in与exists性能比较
1) 与in/exists等价的连接查询
select * from T1 where x in ( select y from T2 where z = Z) 等价于
select t1.*from t1 , (select *from t2 where z = Z) t2 where t1.x = t2.x而非
select t1.* from t1,t2 where t1.x = t2.x and t2.z = Z
select * from T1 where x exists ( select null from T2 where t1.x = t2.y and z = Z)等价于
select distinct t1.* from T1 , T2 where t1.x = t2.y and t2.z = Z
(如果在t1的x和t2的y上均有唯一主键，则distinct可省略)
测试如下：
truncate table outer_large_t
/
truncate table inner_large_t
/
insert into outer_large_t values(1,'test1','test1')
/
insert into outer_large_t values(2,'test2','test3')
/
insert into outer_large_t values(3,'test3','test2')
/
insert into inner_large_t values(1,'test1','test')
/
insert into inner_large_t values(1,'test1','test')
/
insert into inner_large_t values(1,'test3','test')
/
commit
/
test@GZSERVER> select * from outer_large_t where id in (select id from inner_lar
ge_t where c3 = 'test1')
2 /
ID C1 C2
------------ ---------- ----------
1 test1 test1
test@GZSERVER> select t1.* from outer_large_t t1,inner_large_t t2
2 where t1.id = t2.id
3 and t2.c3 = 'test1'
4 /
ID C1 C2
------------ ---------- ----------
1 test1 test1
1 test1 test1
test@GZSERVER> select t1.* from outer_large_t t1,(select distinct id from inner_
large_t t2 where c3 = 'test1') t2
2 where t1.id = t2.id
3 /
ID C1 C2
------------ ---------- ----------
1 test1 test1
test@GZSERVER> select * from outer_large_t t1
2 where exists (select null from inner_large_t t2 where t1.id = t2.id and t2.
c3 = 'test1')
3 /
ID C1 C2
------------ ---------- ----------
1 test1 test1
test@GZSERVER> select t1.* from outer_large_t t1,inner_large_t t2
2 where t1.id = t2.id
3 and t2.c3 = 'test1'
4 /
ID C1 C2
------------ ---------- ----------
1 test1 test1
1 test1 test1
test@GZSERVER> select distinct t1.* from outer_large_t t1,inner_large_t t2
2 where t1.id = t2.id
3 and t2.c3 = 'test1'
4 /
ID C1 C2
------------ ---------- ----------
1 test1 test1
2) in与exists性能比较
测试数据
truncate table outer_large_t
/
truncate table outer_small_t
/
truncate table inner_large_t
/
truncate table inner_small_t
/
declare
begin
for i in 1..50000 loop
insert into outer_large_t values (i,'test','test');
end loop;
for i in 49000..50000 loop
insert into outer_small_t values (i,'test','test');
end loop;
for i in 30000..100000 loop
insert into inner_large_t values (i,'test','test');
end loop;
for i in 1..1000 loop
insert into inner_small_t values (i,'test','test');
end loop;
commit;
end;
/
analyze table outer_large_t compute statistics for table for all indexes
/
analyze table outer_small_t compute statistics for table for all indexes
/
analyze table inner_large_t compute statistics for table for all indexes
/
analyze table inner_small_t compute statistics for table for all indexes
/
-- 下面比较各个版本的CBO在执行计划上的不同
-- Oracle 10g
test@GZSERVER> set autotrace on exp
test@GZSERVER> alter session set OPTIMIZER_FEATURES_ENABLE='10.1.0'
2 /
会话已更改。
test@GZSERVER> select count(*) from outer_large_t where id in (select id from inner_large_t)
2 /
COUNT(*)
----------
20001
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=278 Card=1 Bytes=2
6)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=278 Card=50000 Bytes=1300000)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)
(Cost=23 Card=50000 Bytes=650000)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=42 Card=70001 Bytes=910013)
test@GZSERVER> select count(*) from outer_large_t t1
2 where exists (select null from inner_large_t t2 where t1.id = t2.id)
3 /
COUNT(*)
----------
20001
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=278 Card=1 Bytes=2
6)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=278 Card=50000 Bytes=1300000)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_LARGE_T' (INDEX)
(Cost=23 Card=50000 Bytes=650000)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=42 Card=70001 Bytes=910013)
test@GZSERVER> select count(*) from outer_small_t where id in (select id from inner_large_t)
2 /
COUNT(*)
----------
1001
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=45 Card=1 Bytes=26
)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=45 Card=1001 Bytes=26026)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE
X) (Cost=2 Card=1001 Bytes=13013)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=42 Card=70001 Bytes=910013)
test@GZSERVER> select count(*) from outer_small_t t1
2 where exists (select null from inner_large_t t2 where t1.id = t2.id)
3 /
COUNT(*)
----------
1001
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=45 Card=1 Bytes=26
)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=45 Card=1001 Bytes=26026)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE
X) (Cost=2 Card=1001 Bytes=13013)
4 2 INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX
) (Cost=42 Card=70001 Bytes=910013)
-- Oracle 9.2.0
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-21641/，如需转载，请注明出处，否则将追究法律责任。
