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代码执行时间很长，请问该怎样去优化呢
你使用了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
嵌套查询过多，没有表结构很难给你具体优化建议，我给你提几个方向：
1、在查询条件列加索引，这个效果估计不大，索引过多会影响插入效率；
2、将子查询变成实体视图，这样查询效率变高，但是代码里面会需要额外加些存储过程进行数据校验