在Oralce中我们经常会使用exists、not exists来进行一些表连接操作，在普通情况下Oracle的
执行计划如果使用到semi-join、anti-join方式来进行表连接的话，速度一遍很快，否则很慢。通
过使用提示hash_sj hash_aj也可使执行计划走semi-join、anti-join。
但是在有些情况下，即使增加了提示，或者对表进行了分析后，执行计划也不走semi-join、anti-join
方式，下面就是对这些情况的讨论。
附注：本文中的例子均是在Oracle9.2.0.6 for linux中测试的。
1、在where条件中多个exists、not exists是or的关系
SQL> create table a1 as select * from dba_objects where rownum <= 10000;
SQL> create table a2 as
select * from (select t.*,rownum rd from dba_objects t) p where rd>5000 and rd<= 15000;
SQL> create table a3 as
select * from (select t.*,rownum rd from dba_objects t) p where rd>7500 and rd<= 17500;
SQL> analyze table a1 compute statistics;
SQL> analyze table a2 compute statistics;
SQL> analyze table a3 compute statistics;
SQL> set autotrace on exp
SQL> set timing on
----- exists or exists 情况
SQL> select a1.owner,count(*)
2 from a1
3 where exists
4 (select /*+ hash_sj(a1 a2) */* from a2
5 where a2.object_id=a1.object_id)
6 or
7 exists
8 (select /*+ hash_sj(a1 a2) */* from a3
9 where a3.object_id=a1.object_id)
10 group by a1.owner;
OWNER COUNT(*)
------------------------------ ----------
SYS 5000
已用时间: 00: 00: 26.66
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=1 Bytes=7)
1 0 SORT (GROUP BY) (Cost=18 Card=1 Bytes=7)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'A1' (Cost=17 Card=975 Bytes=6825)
4 2 TABLE ACCESS (FULL) OF 'A2' (Cost=17 Card=1 Bytes=4)
5 2 TABLE ACCESS (FULL) OF 'A3' (Cost=17 Card=1 Bytes=4)
执行计划中显然没有用到semi-jobin，这时候只有改写SQL，通过union all的方式来达到使用到
semi-join的目的。
SQL> select x.owner,count(*)
2 from (select * from a1
3 where exists
4 (select /*+ hash_sj(a1 a2)*/* from a2
5 where a2.object_id= a1.object_id )
6 union all
7 select * from a1
8 where not exists
9 (select /*+ hash_aj(a1 p)*/* from a2 p
10 where p.object_id=a1.object_id)
11 and exists
12 (select /*+ hash_sj(a1 a3)*/* from a3
13 where a3.object_id=a1.object_id)
14 ) x
15 group by x.owner;
OWNER COUNT(*)
------------------------------ ----------
SYS 5000
已用时间: 00: 00: 00.31
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=90 Card=10001 Bytes=
170017)
1 0 SORT (GROUP BY) (Cost=90 Card=10001 Bytes=170017)
2 1 VIEW (Cost=88 Card=10001 Bytes=170017)
3 2 UNION-ALL
4 3 HASH JOIN (SEMI) (Cost=35 Card=10000 Bytes=200000)
5 4 TABLE ACCESS (FULL) OF 'A1' (Cost=17 Card=10000 Bytes=70000)
6 4 VIEW OF 'VW_SQ_2' (Cost=17 Card=10000 Bytes=130000)
7 6 TABLE ACCESS (FULL) OF 'A2' (Cost=17 Card=10000Bytes=40000)
8 3 HASH JOIN (ANTI) (Cost=53 Card=1 Bytes=24)
9 8 HASH JOIN (SEMI) (Cost=35 Card=10000 Bytes=200000)
10 9 TABLE ACCESS (FULL) OF 'A1' (Cost=17 Card=10000 Bytes=70000)
11 9 VIEW OF 'VW_SQ_1' (Cost=17 Card=10000 Bytes=130000)
12 11 TABLE ACCESS (FULL) OF 'A3' (Cost=17 Card=10000 Bytes=40000)
13 8 TABLE ACCESS (FULL) OF 'A2' (Cost=17 Card=10000 Bytes=40000)
----- not exists or not exists 情况
SQL> select a1.owner,count(*)
2 from a1
3 where not exists
4 (select /*+ hash_sj(a1 a2) */* from a2
5 where a2.object_id=a1.object_id)
6 or
7 not exists
8 (select /*+ hash_sj(a1 a2) */* from a3
9 where a3.object_id=a1.object_id)
10 group by a1.owner;
OWNER COUNT(*)
------------------------------ ----------
SYS 7500
已用时间: 00: 00: 20.72
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=1 Bytes=7)
1 0 SORT (GROUP BY) (Cost=18 Card=1 Bytes=7)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'A1' (Cost=17 Card=975 Bytes=6825)
4 2 TABLE ACCESS (FULL) OF 'A2' (Cost=17 Card=1 Bytes=4)
5 2 TABLE ACCESS (FULL) OF 'A3' (Cost=17 Card=1 Bytes=4)
SQL> select x.owner,count(*)
2 from (select * from a1
3 where not exists
4 (select /*+ hash_sj(a1 a2)*/* from a2
5 where a2.object_id= a1.object_id )
6 union all
7 select * from a1
8 where exists
9 (select /*+ hash_aj(a1 p)*/* from a2 p
10 where p.object_id=a1.object_id)
11 and not exists
12 (select /*+ hash_sj(a1 a3)*/* from a3
13 where a3.object_id=a1.object_id)
14 ) x
15 group by x.owner;
OWNER COUNT(*)
------------------------------ ----------
SYS 7500
已用时间: 00: 00: 00.31
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=89 Card=2 Bytes=34)
1 0 SORT (GROUP BY) (Cost=89 Card=2 Bytes=34)
2 1 VIEW (Cost=88 Card=2 Bytes=34)
3 2 UNION-ALL
4 3 HASH JOIN (ANTI) (Cost=35 Card=1 Bytes=11)
5 4 TABLE ACCESS (FULL) OF 'A1' (Cost=17 Card=10000 Bytes=70000)
6 4 TABLE ACCESS (FULL) OF 'A2' (Cost=17 Card=10000 Bytes=40000)
7 3 HASH JOIN (SEMI) (Cost=53 Card=1 Bytes=24)
8 7 HASH JOIN (ANTI) (Cost=35 Card=1 Bytes=11)
9 8 TABLE ACCESS (FULL) OF 'A1' (Cost=17 Card=10000 Bytes=70000)
10 8 TABLE ACCESS (FULL) OF 'A3' (Cost=17 Card=10000 Bytes=40000)
11 7 VIEW OF 'VW_SQ_1' (Cost=17 Card=10000 Bytes=130000)
12 11 TABLE ACCESS (FULL) OF 'A2' (Cost=17 Card=10000 Bytes=40000)
----- not exists or exists 情况
SQL> select a1.owner,count(*)
2 from a1
3 where not exists
4 (select /*+ hash_sj(a1 a2) */* from a2
5 where a2.object_id=a1.object_id)
6 or exists
7 (select /*+ hash_sj(a1 a3) */* from a3
8 where a3.object_id=a1.object_id)
9 group by a1.owner;
OWNER COUNT(*)
------------------------------ ----------
SYS 7500
已用时间: 00: 00: 20.57
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=1 Bytes=7)
1 0 SORT (GROUP BY) (Cost=18 Card=1 Bytes=7)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'A1' (Cost=17 Card=975 Bytes=6825)
4 2 TABLE ACCESS (FULL) OF 'A2' (Cost=17 Card=1 Bytes=4)
5 2 TABLE ACCESS (FULL) OF 'A3' (Cost=17 Card=1 Bytes=4)
SQL> select x.owner,count(*)
2 from (select * from a1
3 where not exists
4 (select /*+ hash_sj(a1 a2)*/* from a2
5 where a2.object_id= a1.object_id )
6 union all
7 select * from a1
8 where exists
9 (select /*+ hash_aj(a1 p)*/* from a2 p
10 where p.object_id=a1.object_id)
11 and exists
12 (select /*+ hash_sj(a1 a3)*/* from a3
13 where a3.object_id=a1.object_id)
14 ) x
15 group by x.owner;
OWNER COUNT(*)
------------------------------ ----------
SYS 7500
已用时间: 00: 00: 00.32
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=90 Card=10001 Bytes=
170017)
1 0 SORT (GROUP BY) (Cost=90 Card=10001 Bytes=170017)
2 1 VIEW (Cost=88 Card=10001 Bytes=170017)
3 2 UNION-ALL
4 3 HASH JOIN (ANTI) (Cost=35 Card=1 Bytes=11)
5 4 TABLE ACCESS (FULL) OF 'A1' (Cost=17 Card=10000 Bytes=70000)
6 4 TABLE ACCESS (FULL) OF 'A2' (Cost=17 Card=10000 Bytes=40000)
7 3 HASH JOIN (SEMI) (Cost=53 Card=10000 Bytes=330000)
8 7 HASH JOIN (SEMI) (Cost=35 Card=10000 Bytes=200000)
9 8 TABLE ACCESS (FULL) OF 'A1' (Cost=17 Card=10000 Bytes=70000)
10 8 VIEW OF 'VW_SQ_2' (Cost=17 Card=10000 Bytes=130000)
11 10 TABLE ACCESS (FULL) OF 'A3' (Cost=17 Card=10000 Bytes=40000)
12 7 VIEW OF 'VW_SQ_1' (Cost=17 Card=10000 Bytes=130000)
13 12 TABLE ACCESS (FULL) OF 'A2' (Cost=17 Card=10000 Bytes=40000)
从上面的例子可以看出速度的提高是非常明显的，当然对于or的情况越多，修改后的SQL也就越复杂，不过相对于效率
的提高来说还是很值得的。
2、如果子句中含有group by这种排序操作,使用in来代替exists操作会大大提高效率,示例如下：
修改一下示例表：
SQL> insert into a2 select * from a2;
SQL> commit;
<1> 使用exists操作,无论添加什么提示都不能使其走semi-join,耗费大量时间
SQL> select count(*) from a1 a
2 where exists
3 ( select /*+ hash_sj(a b) */*
4 from ( select object_id from a2
5 group by object_id having count(*) > 1
6 ) b
7 where a.object_id=b.object_id
8 );
COUNT(*)
----------
5000
已用时间: 00: 00: 46.22
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'A1' (Cost=17 Card=500 Bytes=2000)
4 2 FILTER
5 4 SORT (GROUP BY NOSORT) (Cost=17 Card=1 Bytes=4)
6 5 TABLE ACCESS (FULL) OF 'A2' (Cost=17 Card=1 Bytes=4)
<2> 将exists改为in句型,就可以用到semi-join,效率大大提高
SQL> select count(*) from a1 a where object_id in
2 ( select object_id from a2 b
3 group by object_id having count(*) > 1
4 );
COUNT(*)
----------
5000
已用时间: 00: 00: 00.90
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (SEMI) (Cost=37 Card=500 Bytes=8500)
3 2 TABLE ACCESS (FULL) OF 'A1' (Cost=17 Card=10000 Bytes=40000)
4 2 VIEW OF 'VW_NSO_1' (Cost=19 Card=500 Bytes=6500)
5 4 FILTER
6 5 SORT (GROUP BY) (Cost=19 Card=500 Bytes=2000)
7 6 TABLE ACCESS (FULL) OF 'A2' (Cost=17 Card=10000 Bytes=40000)
对于not exists情况也是同样的,需要将语句改写为not in,但是要加上提示以及关联的列要有非空的限制
才能使其走anti-join的执行计划。
SQL> select count(*) from a1 a
2 where not exists
3 ( select /*+ hash_aj(a b)*/* from
4 ( select object_id from a2
5 group by object_id having count(*) > 1
6 ) b
7 where a.object_id=b.object_id
8 );
COUNT(*)
----------
5000
已用时间: 00: 00: 46.47
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'A1' (Cost=17 Card=500 Bytes=2000)
4 2 FILTER
5 4 SORT (GROUP BY NOSORT) (Cost=17 Card=1 Bytes=4)
6 5 TABLE ACCESS (FULL) OF 'A2' (Cost=17 Card=1 Bytes=4)
SQL> select count(*) from a1 a
2 where object_id is not null
3 and object_id not in
4 ( select object_id from a2 b
5 where object_id is not null
6 group by object_id having count(*) > 1
7 );
COUNT(*)
----------
5000
已用时间: 00: 00: 00.75
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (ANTI) (Cost=37 Card=9500 Bytes=161500)
3 2 TABLE ACCESS (FULL) OF 'A1' (Cost=17 Card=10000 Bytes=40000)
4 2 VIEW OF 'VW_NSO_1' (Cost=19 Card=500 Bytes=6500)
5 4 FILTER
6 5 SORT (GROUP BY) (Cost=19 Card=500 Bytes=2000)
7 6 TABLE ACCESS (FULL) OF 'A2' (Cost=17 Card=10000 Bytes=40000)
3151
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
