ITPub博客

首页 > 应用开发 > IT综合 > 关于exists、in系列的补充说明

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

原创 IT综合 作者:yaanzy 时间:2006-02-09 15:02:13 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 使用vnc访问linux
请登录后发表评论 登录
全部评论
  • 博文量
    108
  • 访问量
    766811