- 论坛徽章:
- 0
|
在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内查询出结果
高难度的挑战
|
|