sql join 子查询_SQL优化-秒级优化，hint让IN子查询当驱动表

在sql优化中，一个比较重要的一点就是驱动表的选择，HASH JOIN和NEST LOOP来说驱动表的选择是至关重要的。
对于NEST LOOP说最好是小表作为驱动表，与大表的连接键上减少索引，并且保证索引的选择性比较好。
对于HASH JOIN小表作为驱动表是要HASH进内存的，所以也要保证不能太大，如果HASH JOIN的都是大表，我们会在日后来分析怎么优化。
具体SQL是走HASH JOIN好还是NEST LOOP好， 日后也会说说我的理解。
今天说的这个案例是存在IN的子查询，其实原理相同。
select ht.fwid as fwid,
ht.htcode as htcode,
ht.id as htid,
ht.htbadjh as htbadjh,
ht.fwdmffbh as fwdmffbh,
cxmsfxm as cxmsfxm,
cxmsfzjhm as cxmsfzjhm,
ht.fwdmxzqhdm as fwdmxzqhdm,
ht.fwdm as fwdm,
ht.fwdz as fwdz,
ht.jzmj as jzmj,
ht.fwyt as fwyt,
ht.qdrq as qdrq,
ht.basj as basj
from ha_SPFHT.tpn_xshtqd ht where
id in
( select htid
from ha_SPFHT.tpn_htjydx jydx
where jydx.rylx = 2
and jydx.ryxm like '%XXX%'
and (jydx.zjhm is null or jydx.zjlb is null or (jydx.zjlb != 1 or jydx.zjhm like '%E122531385%'))
)
子查询返回2条数据,整个SQL要运行几十分钟。
SQL走的是HASH JOIN, 理论上没什么问题，但是我们有更好的选择。

select /*+ leading(jydx) use_nl(ht,jydx) */ ht.fwid as fwid, --1.5S---0.5S
ht.htcode as htcode,
ht.id as htid,
ht.htbadjh as htbadjh,
ht.fwdmffbh as fwdmffbh,
cxmsfxm as cxmsfxm,
cxmsfzjhm as cxmsfzjhm,
ht.fwdmxzqhdm as fwdmxzqhdm,
ht.fwdm as fwdm,
ht.fwdz as fwdz,
ht.jzmj as jzmj,
ht.fwyt as fwyt,
ht.qdrq as qdrq,
ht.basj as basj
from ha_SPFHT.tpn_xshtqd ht where
id in
( select htid
from ha_SPFHT.tpn_htjydx jydx
where jydx.rylx = 2
and jydx.ryxm like '%XXX%'
and (jydx.zjhm is null or jydx.zjlb is null or (jydx.zjlb != 1 or jydx.zjhm like '%E122531385%'))
)

在ryxm 建立索引了

最后sql在秒级完成