请教一个oracle高难度的查询SQL优化问题
在Oracle 11g中执行以下sql需要4s,其中表A为记录表(主表),30万条记录,表B为记录转发表(从表),150万记录:select count(*) from 表A t1 where 1=1 and issend =0 and (select count(distinct ctrl_kind) from 表B t4 where t4.doc_info_id=t1.doc_receive_regin_id and t4.dept_code='10005000' and (t4.ctrl_kind=5) and t4.doc_info_kind>0 and rownum<=1)=1
或等价的sql:
select count(*) from 表A t1 where 1=1 and issend =0 and exsits( (select * from 表B t4 where t4.doc_info_id=t1.doc_receive_regin_id and t4.dept_code='10005000' and(t4.ctrl_kind=5) and t4.doc_info_kind>0 and rownum<=1) )
表A的doc_receive_regin_id已建索引,表B的doc_info_id和dept_code已建索引,且根据dept_code分区
有没有大神能在oracle 11g环境下优化这个查询啊,要求实现1s内查询出结果
高难度的挑战
帮顶。楼主能否提供一些更详细的信息呀?比如执行计划或者是10046事件等等。 回复ls,count(*)是43412条,如果select * from 表A t1 where 1=1 and issend =0 and (select count(distinct ctrl_kind) from 表B t4 where t4.doc_info_id=t1.doc_receive_regin_id and t4.dept_code='10005000' and (t4.ctrl_kind=5) and t4.doc_info_kind>0 and rownum<=1)=1只要700多ms,但是count总数却要4s
看count总数时,执行计划是全表扫描表A的,但是select记录时的执行计划也是全表扫描表A的,但却只要700多ms 执行计划贴上来,count表的时候肯定是全表扫描,但是你的SQL不一定是全表扫描,很可能是index full scan :oo:outu::sleepy::((:dizzy::oo 新手膜拜
页:
[1]