# IN/EXISTS/NOT IN/NOT EXISTS(2-1)

4.         in & exist

1)        in/exists等价的连接查询

2)        inexists性能比较

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

(如果在t1xt2y上均有唯一主键，则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)      inexists性能比较

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数据库版块资深版主，对Oracle、PostgreSQL有深入研究。

• 博文量
1559
• 访问量
4241575