2 polaris077 polaris077 于 2014.01.14 11:15 提问

关于oracle数据库索引

执行sql如下:
select *
from lm3.cc03
where aae017 in (
select distinct natl.jgid
from be3.agency_natl natl,
be3.agency_config conf
where natl.jgid = conf.jgid
and conf.ywlb = '02'
start with natl.jgid = '37100106'
connect by prior conf.jgid = conf.sjjgid

)
and aae036 >= to_date('20130101', 'yyyyMMdd')

lm3.cc03表中有aae017和aae036 的组合索引,也有aae036 的单独索引。
但是执行以上sql时,lm3.cc03检索用不上索引,为什么?
哪位大侠给指导一下,谢谢!

2个回答

lzp_lrp
lzp_lrp   Ds   Rxr 2014.12.28 18:58

不要用in,改为exists

lzp_lrp
lzp_lrp   Ds   Rxr 2014.12.28 18:59

select *
from lm3.cc03
where exists (
select distinct natl.jgid
from be3.agency_natl natl,
be3.agency_config conf
where natl.jgid = conf.jgid
and natl.jgid = aae017
and conf.ywlb = '02'
start with natl.jgid = '37100106'
connect by prior conf.jgid = conf.sjjgid

)

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!