注:以下内容适合 初学oracle开发或者java等开发者,高手略过
一 exists&in
以下三个语句 功能都是从 iodso.qos_hisentry_sheet_jtext_td 里面找到 sheet_no在 iodso.qos_hisentry_sheet_td表 arch_time 1天时间里面的单子。
iodso.qos_hisentry_sheet_jtext_td 有个普通的联合索引
iodso.qos_hisentry_sheet_td 有个普通的索引
两个表的数据量情况
select count(1) from iodso.qos_hisentry_sheet_td-- 29843027
select count(1) from iodso.qos_hisentry_sheet_jtext_td--29973242
1
select *
from iodso.qos_hisentry_sheet_jtext_td t
where t.sheet_no in (select a.sheet_no
from iodso.qos_hisentry_sheet_td a
where a.arch_time between trunc(sysdate - 1, 'dd') and
trunc(sysdate, 'dd'));
2
select *
from iodso.qos_hisentry_sheet_jtext_td t
where t.sheet_no in (select a.sheet_no
from iodso.qos_hisentry_sheet_td a
where a.arch_time between trunc(sysdate - 1, 'dd') and
trunc(sysdate, 'dd')
and t.sheet_no = a.sheet_no);
3
select *
from iodso.qos_hisentry_sheet_jtext_td t
where exists (select a.sheet_no
from iodso.qos_hisentry_sheet_td a
where a.arch_time between trunc(sysdate - 1, 'dd') and
trunc(sysdate, 'dd')
and t.sheet_no = a.sheet_no);
执行计划比较
执行计划由pl/sql Dev的F5键生成 ,一般看执行计划会建议从sqlplus explain plan for看 但是开发者可能更习惯用pl、sql工具
且工具能定位到第一个执行的地方 且对应的操作描述 在最下方有一串英文 如下图 sort_unique 的解释 在最下面红圈的地方 sort a result set and eliminate duplicates 意思是对结果集排序并且去重
sql 1 的计划:
sql 3 的计划:
sql 2的计划:
从上面的执行计划及顺序来看 三个sql 完全一样。
执行结果
sql 1的执行结果:
sql2 的执行结果:
sql3 的执行结果:
从以上来看 是sql1 执行的最快 sql2 执行的最慢
上面是从查小表的情况 再看看下面语句的情况(查大表的情况):
1
select a.*
from iodso.qos_hisentry_sheet_td a
where a.arch_time between trunc(sysdate - 1, 'dd') and
trunc(sysdate, 'dd')
and sheet_no in
(select sheet_no from iodso.qos_hisentry_sheet_jtext_td t);
2
select a.*
from (select *
from iodso.qos_hisentry_sheet_td a
where a.arch_time between trunc(sysdate - 1, 'dd') and
trunc(sysdate, 'dd')) a
where exists (select t.sheet_no
from iodso.qos_hisentry_sheet_jtext_td t
where t.sheet_no = a.sheet_no);
所以 网上很多说的 exists 比 in快 或者 检索大表的时候 exists比 in快 等等 不一定都是准确的,现在百度的很多东西可能都是复制来复制去,还有的是以前8i 9i老版本的规则 现在基本都是10g以上 不一定适用。网上的结论要慎用 最好自己试验下。
exists 和 in的效率通常情况是差不多的,需要看执行计划及实际上执行时间为准,。
ps:大部分的企业级开发者可能更喜欢用in 易于平常的思维理解
二 not exists¬ in
1
select t.occur_area_id-1,
COUNT(1) ALL_NUM,
SUM(CASE
WHEN (DECODE(SIGN(T.FLOW_TIME - t.fact_flow_time), -1, 0, 1) = 0) THEN
1
ELSE
0
END) CS_NUM
from QOS_NET_CONTROL_GD_sb T
where t.sheet_no not in
(SELECT t1.sheet_no
FROM QOS_NET_CONTROL_GD_sb T1,
IODSO.QOS_EOSORG_T_EMPLOYEE T2,
IODSO.QOS_EOSORG_T_ORGANIZATION T3,
iodso.qos_eosoperator t6
WHERE T1.USERID = T6.userid
and t6.operatorid = t2.operatorid
and t2.orgid=t3.orgid
and T1.STAT_DATE = TO_DATE('2014-11-08', 'YYYY-MM-DD')
AND T1.STAT_DATE = TO_DATE('2014-11-08', 'YYYY-MM-DD')
)
group by t.occur_area_id;
2
select t.occur_area_id - 1,
COUNT(1) ALL_NUM,
SUM(CASE
WHEN (DECODE(SIGN(T.FLOW_TIME - t.fact_flow_time), -1, 0, 1) = 0) THEN
1
ELSE
0
END) CS_NUM
from QOS_NET_CONTROL_GD_sb T
where not exists
(select 1
from QOS_NET_CONTROL_GD_sb s,
IODSO.QOS_EOSORG_T_EMPLOYEE T2,
IODSO.QOS_EOSORG_T_ORGANIZATION T3,
iodso.qos_eosoperator t6
where T.Sheet_No = s.sheet_no
and s.USERID = T6.userid
and t6.operatorid = t2.operatorid
and t2.orgid = t3.orgid)
and T.STAT_DATE = TO_DATE('2014-11-08', 'YYYY-MM-DD')
group by t.occur_area_id
从上面执行计划可以看到 cost 差别很大 ,not exists 比not in 的小很多。 not exists使用的是hash join anti 而 not in 使用的是filter。执行时间来看 not exists 几分钟 not in 执行了30分钟还没完成。
小总结:(此内容转)
Semi-join
通常出现在使用了exists或in的sql中,所谓semi-join即在两表关联时,当第二个表中存在一个或多个匹配记录时,返回第一个表的记录;
与普通join的区别在于semi-join时,第一个表里的记录最多只返回一次
Anti-join
第二张表没有发现匹配记录时,才会返回第一张表里的记录;
何时选择anti-join1
使用not in且相应列有not null约束
not exists,不保证每次都用到anti-join
当无法选择anti-join时,oracle常会采用filter替代
filter
是对外表的每一行,都要对内表执行一次全表扫描,他其实很像我们熟悉的neested loop,但它的独特之处在于会维护一个hash table
三 两个表根据某字段关联更新
update ap
set ap.t =
(select bp.t from bp where ap.s = bp.s)
where exists (select 1 from bp where ap.s = bp.s);
commit;
语句看似很简单 但是当ap bp本身都是很复杂的查询的时候 可能想到这个比较困难了。