请教一个oracle高难度的查询SQL优化问题

Question

select count(1)
  from 表A t1
 where 1 = 1
   and issend = 0
   and exsits (select 'x'
          from (select t4.doc_info_id
                  from 表B t4
                 where t4.dept_code = '10005000'
                   and t4.ctrl_kind = 5
                   and t4.doc_info_kind > 0
                   and rownum <= 1) t5
         where t5.doc_info_id = t1.doc_receive_regin_id)

Answer 1

表B根据dept_code做HASH分区，按dept_code，ctrl_kind，doc_info_kind建索引
表A按doc_receive_regin_id,issend建索引

Answer 2

1.建议先去掉rownum的条件，rownum对导致oracle查询谓词前推失效(最好去掉和不去掉两个都测试下)
2.表B不要用*，只需要返回必要的字段就可以
3.确认下表分区后10005000分区数据量，看还能否再加过滤条件，比如时间,你的SQL可能DOC_RECEIVE_REGIN_ID字段上的索引也没用到的，建议再把执行计划发出来(用autotrace输出的执行计划)给你个参考的SQL：
WITH T AS
 (SELECT T4.DOC_INFO_ID
FROM 表B T4
   WHERE T4.DEPT_CODE = '10005000'
 AND T4.CTRL_KIND = 5
 AND T4.DOC_INFO_KIND > 0)
SELECT COUNT(*)
  FROM 表A T1
 WHERE 1 = 1
   AND ISSEND = 0
   AND EXSITS (SELECT 1
  FROM T T4
 WHERE T.DOC_INFO_ID = T1.DOC_RECEIVE_REGIN_ID
   AND ROWNUM <= 1);

Answer 3

select a.CNT
  from (select count(distinct *)
          from 表A t1
         where 1 = 1
           and issend = 0)
  join (select doc_receive_regin_id
          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)
    on t1.doc_receive_regin_id = t4.doc_receive_regin_id)

Answer 4

select count(distinct(a.*))
  from (select *
          from 表A t1
         where 1 = 1
           and issend = 0)
  join (select doc_receive_regin_id
          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)
    on t1.doc_receive_regin_id = t4.doc_receive_regin_id)a