一个exists取代in的例子（分析两个执行计划的差别）
感受一些哪个sql更顺眼一些。SQL 优化HASH JOIN SEMI案例:
LATCH 严重，主机CPU IDLE<10%（应该说明是那种类别的latch争用严重）
绑定变量已经被实际值替换
select groupinsta0_.MEMBER_ID as MEMBER1_33_,
groupinsta0_.LIFE_CYCLE_ID as LIFE2_33_,
groupinsta0_.SERV_ID as SERV3_33_,
groupinsta0_.GROUP_ID as GROUP4_33_,
groupinsta0_.MEMBER_DESC as MEMBER5_33_,
groupinsta0_.MEMBER_TYPE_IDas MEMBER6_33_,
groupinsta0_.MEMBER_ROLE_IDas MEMBER7_33_,
groupinsta0_.MEMBER_OBJECT_ID as MEMBER8_33_,
groupinsta0_.ACC_NBR as ACC9_33_,
groupinsta0_.KENAN_FLAG as KENAN10_33_
from zk.GROUP_INSTANCE_MEMBER groupinsta0_
where groupinsta0_.GROUP_ID = '18972206'
and (groupinsta0_.LIFE_CYCLE_ID in
(select lifecycle1_.LIFE_CYCLE_ID
from zk.LIFE_CYCLE lifecycle1_
where lifecycle1_.EXP_DATE >= sysdate));
ID Operation OBJECT_NAME COST CARD BYTES
SELECT STATEMENT 227
HASH JOIN 227 132 8712
TABLE ACCESS BY INDEX ROWID GROUP_INSTANCE_MEMBER 133 132 6864
INDEX RANGE SCAN I_FKK_GROUP_INSTANCE_ 270 3 135
TABLE ACCESS BY INDEX ROWID LIFE_CYCLE 2 11890452 166466328
INDEX RANGE SCAN IDX_LIFE_CYCLE_EXP_DATE 1 96
（注意：不细看，这个执行计划是完美的。因此，我们在分析执行计划的时候，要详细的结合实际的SQL语句和统计信息，对执行计划进行分析。）
一看就是select lifecycle1_.LIFE_CYCLE_ID from zk.LIFE_CYCLE lifecycle1_ where lifecycle1_.EXP_DATE >= sysdate
返回的结果集太大，导致latch: cache buffers chains
但从执行上看HASH JOIN 的适合的情况是遇到一大一小两个结果集，条件groupinsta0_.GROUP_ID = '18972206' 的结果集才2条，而lifecycle1_.EXP_DATE >= sysdate 的结果集超过1KW
注意（分析的有些问题，我们只是关注两个执行计划，还是有很大的改进）。
目地让执行计划中HASH JOIN 为HASH JOIN SEMI
改写SQL如下：
select groupinsta0_.MEMBER_ID as MEMBER1_33_,
groupinsta0_.LIFE_CYCLE_ID as LIFE2_33_,
groupinsta0_.SERV_ID as SERV3_33_,
groupinsta0_.GROUP_ID as GROUP4_33_,
groupinsta0_.MEMBER_DESC as MEMBER5_33_,
groupinsta0_.MEMBER_TYPE_IDas MEMBER6_33_,
groupinsta0_.MEMBER_ROLE_IDas MEMBER7_33_,
groupinsta0_.MEMBER_OBJECT_ID as MEMBER8_33_,
groupinsta0_.ACC_NBR as ACC9_33_,
groupinsta0_.KENAN_FLAG as KENAN10_33_
from zk.GROUP_INSTANCE_MEMBER groupinsta0_
where groupinsta0_.GROUP_ID = '18972905'
and exists (select 'x'
from zk.LIFE_CYCLE lifecycle1_
where lifecycle1_.EXP_DATE >= sysdate and groupinsta0_.LIFE_CYCLE_ID=lifecycle1_.LIFE_CYCLE_ID);
Elapsed: 00:00:09.65
ID Operation OBJECT_NAME COST CARD BYTES
SELECT STATEMENT 227
HASH JOINSEMI 227132 8712
TABLE ACCESS BY INDEX ROWID GROUP_INSTANCE_MEMBER 133132 6864
INDEX RANGE SCAN I_FKK_GROUP_INSTANCE_270 3 135
TABLE ACCESS BY INDEX ROWID LIFE_CYCLE 2 11890452 166466328
INDEX RANGE SCAN IDX_LIFE_CYCLE_EXP_DATE 1 96
页:
[1]