关于exists、in系列的补充说明

1、在Oracle8i中，当使用exists时，如果两表连接字段使用函数或||之类的特殊处理，
其执行计划不能走hash semi的半连接，这时需要将其改成in的格式来处理，9i中
就不存在这种情况

[@more@]

Oracle 8i 中：

select count(*)
from t_cp_dz_user t
where t.strdatetime > '0602'
and exists
(select /*+ hash_sj(t q)*/* from t_200602_yd_3 q
where q.mpn=substr(t.strusernumber,3) )

执行计划：

SELECT STATEMENT, GOAL = CHOOSE
SORT AGGREGATE
FILTER
TABLE ACCESS FULL Object owner=SMS
TABLE ACCESS FULL Object owner=SMS


select count(*)
from t_cp_dz_user t
where t.strdatetime > '0602'
and substr(t.strusernumber,3) in
(select /*+ hash_sj(t q)*/q.mpn from t_200602_yd_3 q )

执行计划：

SELECT STATEMENT, GOAL = CHOOSE
SORT AGGREGATE
HASH JOIN SEMI
TABLE ACCESS FULL Object owner=SMS
TABLE ACCESS FULL Object owner=SMS

Oracle 9i 中：

select count(*)
from t_cp_dz_user t
where t.strdatetime > '0602'
and exists
(select /*+ hash_sj(t q)*/* from t_200602_yd_3 q
where q.mpn=substr(t.strusernumber,3) )

执行计划：

SELECT STATEMENT, GOAL = CHOOSE
SORT AGGREGATE
HASH JOIN SEMI
TABLE ACCESS FULL Object owner=SMS
TABLE ACCESS FULL Object owner=SMS

2、在Oracle8i中，当使用not exists时，如果两表连接字段使用函数或||之类的特殊处理，
其执行计划不能走hash join anti的反连接，改成not in的格式也不行，这是得考虑其
它方式，比如改成两个表外连接形式的嵌套查询。9i中就不存在这种情况

Oracle 8i 中：

select count(*)
from t_cp_dz_user t
where t.strdatetime > '0602'
and not exists
(select /*+ hash_sj(t q)*/* from t_200602_yd_3 q
where q.mpn=substr(t.strusernumber,3) )

执行计划：

SELECT STATEMENT, GOAL = CHOOSE
SORT AGGREGATE
FILTER
TABLE ACCESS FULL Object owner=SMS
TABLE ACCESS FULL Object owner=SMS


select count(*)
from t_cp_dz_user t
where t.strdatetime > '0602'
and substr(t.strusernumber,3) in
(select /*+ hash_sj(t q)*/q.mpn from t_200602_yd_3 q )

执行计划：

SELECT STATEMENT, GOAL = CHOOSE
SORT AGGREGATE
FILTER
TABLE ACCESS FULL Object owner=SMS
TABLE ACCESS FULL Object owner=SMS

Oracle 9i 中：

select count(*)
from t_cp_dz_user t
where t.strdatetime > '0602'
and not exists
(select /*+ hash_sj(t q)*/* from t_200602_yd_3 q
where q.mpn=substr(t.strusernumber,3) )

执行计划：

SELECT STATEMENT, GOAL = CHOOSE
SORT AGGREGATE
HASH JOIN ANTI
TABLE ACCESS FULL Object owner=SMS
TABLE ACCESS FULL Object owner=SMS

select count(*)
from t_cp_dz_user t
where t.strdatetime > '0602'
and t.strusernumber is not null
and substr(t.strusernumber,3) not in
(select /*+ hash_aj(t q)*/q.mpn from t_200602_yd_3 q
where q.mpn is not null )

执行计划：

SELECT STATEMENT, GOAL = CHOOSE
SORT AGGREGATE
HASH JOIN ANTI
TABLE ACCESS FULL Object owner=SMS
TABLE ACCESS FULL Object owner=SMS

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3898/viewspace-814282/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3898/viewspace-814282/