oracle代码运行效率低下该怎么优化

Question

with a as
 (select t1.item_id_hosp, t1.hisid, t1.item_name_hosp, t.说明
    from 虚记收费规则库 t
    join zymx t1
      on t.项目编码 = t1.item_id_hosp
   group by t1.item_id_hosp, t1.hisid, t1.item_name_hosp, t.说明
  having sum(t1.num) > 1), 
b as
 (select a.hisid,
         a.item_id_hosp,
         a.item_name_hosp,
         trunc(t2.usage_date) 使用日期,
         t2.unit_price,
         sum(t2.num) num,
         a.说明
    from a
    join zymx t2
      on a.hisid = t2.hisid
     and a.item_id_hosp = t2.item_id_hosp
   group by a.hisid,
            a.item_id_hosp,
            a.item_name_hosp,
            t2.usage_date,
            t2.unit_price,
            a.说明),
c as
 (select b.hisid,
         b.item_id_hosp,
         b.item_name_hosp,
         b.num,
         b.unit_price,
         b.使用日期,
         t4.item_id_hosp   item_id_hosp1,
         t4.item_name_hosp item_name_hosp1,
         b.说明
    from b
    join 虚记收费规则库 t3
      on b.item_id_hosp = t3.项目编码
    join zymx t4
      on b.hisid = t4.hisid
     and b.使用日期 = t4.usage_date
     and t4.item_id_hosp = t3.前置项目编码
   group by b.hisid,
            b.item_id_hosp,
            b.item_name_hosp,
            b.num,
            b.unit_price,
            b.使用日期,
            t4.item_id_hosp,
            t4.item_name_hosp,
            b.说明),
d as
 (select b.hisid,
         b.item_id_hosp,
         b.item_name_hosp,
         b.使用日期,
         b.unit_price,
         b.num,
         b.说明
    from b
   where not exists (select c.hisid from c where b.hisid = c.hisid)),
e as
 (select *
    from (select d.*,
                 nvl((select sum(t5.num)
                       from zymx t5
                      where d.hisid = t5.hisid
                        and d.item_id_hosp = t5.item_id_hosp
                        and t5.num < 0),
                     0) num1
            from d)
   where num + num1 > 0)
select q1.hospital_name 机构名称,
       q1.hospital_id 医疗机构编号,
       q1.zyh 住院号,
       q1.patient_name 姓名,
       q1.patient_gender 性别,
       q1.benefit_type 险种,
       q1.discharge_disease_name_main 诊断,
       q1.hisid 单据编号,
       q.item_id_hosp 违规项目编码,
       q.item_name_hosp 违规项目名称,
       q.使用日期,
       q.说明,
       q.unit_price 单价,
       (q.num + q.num1) 数量,
       q.unit_price * (num + num1) 剔除金额,
       q1.admission_date 入院日期,
       q1.discharge_date 出院日期,
       q1.discharge_dept_name 出院科室,
       q1.bill_date 结算日期,
       q1.zyts 住院天数,
       q1.total_amount 医疗费用总金额
  from e q
  join zyzd q1
    on q.hisid = q1.hisid
 where q1.is_delete = 0

上面这条sql代码执行时间很长，请问该怎样去优化呢

Answer 1

你使用了WITH进行查询分解，那么你应该考虑加提示来固定执行计划，我帮你加了提示固定了执行计划，（注意不要把/*+ 内容 */的内容当成注释给去掉）
基本上会以走哈希，性能会更加稳定。另外非子查询分解的表则让优化器自己选择合适的连接方案（建议表也就是那些不是用with分解的SQL，一定要有合适的索引）

另外你的WITH E表我没帮你优化，因为我不知道数据量，也不知道表结构，如果到e表这里数据量不大就几十行 那用子查询问题不大，但是如果数量很多就不要用子查询，试着改为表关联的方式去 做。

本人擅长Oracle的性能优化，如果你想了解更多信息可以关注我的公众号 唯一的小彬哥

with a as
 (select t1.item_id_hosp, t1.hisid, t1.item_name_hosp, t.说明
    from 虚记收费规则库 t
    join zymx t1
      on t.项目编码 = t1.item_id_hosp
   group by t1.item_id_hosp, t1.hisid, t1.item_name_hosp, t.说明
  having sum(t1.num) > 1),
b as(select /*+ no_merge(a) use_hash(a t2)*/a.hisid,
         a.item_id_hosp,
         a.item_name_hosp,
         trunc(t2.usage_date) 使用日期,
         t2.unit_price,
         sum(t2.num) num,
         a.说明
    from a
    join zymx t2
      on a.hisid = t2.hisid
     and a.item_id_hosp = t2.item_id_hosp
   group by a.hisid,
            a.item_id_hosp,
            a.item_name_hosp,
            t2.usage_date,
            t2.unit_price,
            a.说明),
c as
 (select/*+ no_merge(b) use_hash(b t3)*/ b.hisid,
         b.item_id_hosp,
         b.item_name_hosp,
         b.num,
         b.unit_price,
         b.使用日期,
         t4.item_id_hosp   item_id_hosp1,
         t4.item_name_hosp item_name_hosp1,
         b.说明
    from b
    join 虚记收费规则库 t3
      on b.item_id_hosp = t3.项目编码
    join zymx t4
      on b.hisid = t4.hisid
     and b.使用日期 = t4.usage_date
     and t4.item_id_hosp = t3.前置项目编码
   group by b.hisid,
            b.item_id_hosp,
            b.item_name_hosp,
            b.num,
            b.unit_price,
            b.使用日期,
            t4.item_id_hosp,
            t4.item_name_hosp,
            b.说明),
d as
 (select /*+ no_merge(b)*/ b.hisid,
         b.item_id_hosp,
         b.item_name_hosp,
         b.使用日期,
         b.unit_price,
         b.num,
         b.说明
    from b
   where not exists (select /*+ hash_aj */ c.hisid from c where b.hisid = c.hisid)),
e as
 (select *
    from (select /*+ no_merge(d)*/d.*,
                 nvl((select sum(t5.num)
                       from zymx t5
                      where d.hisid = t5.hisid
                        and d.item_id_hosp = t5.item_id_hosp
                        and t5.num < 0),
                     0) num1
            from d)
   where num + num1 > 0)
select /*+ no_merge(e) use_hash(e q)*/ q1.hospital_name 机构名称,
       q1.hospital_id 医疗机构编号,
       q1.zyh 住院号,
       q1.patient_name 姓名,
       q1.patient_gender 性别,
       q1.benefit_type 险种,
       q1.discharge_disease_name_main 诊断,
       q1.hisid 单据编号,
       q.item_id_hosp 违规项目编码,
       q.item_name_hosp 违规项目名称,
       q.使用日期,
       q.说明,
       q.unit_price 单价,
       (q.num + q.num1) 数量,
       q.unit_price * (num + num1) 剔除金额,
       q1.admission_date 入院日期,
       q1.discharge_date 出院日期,
       q1.discharge_dept_name 出院科室,
       q1.bill_date 结算日期,
       q1.zyts 住院天数,
       q1.total_amount 医疗费用总金额
  from e q
  join zyzd q1
    on q.hisid = q1.hisid
 where q1.is_delete = 0