求助，怎么优化带有多个exists语句，调整过滤顺序

bowshy

SELECT billing_cycle_id,month_id,settle_detail_id,cust_id,section_id,order_finish_date,calc_charge,prd_inst_id,service_nbr,prd_id,prd_completed_date,real_sett_cycle_id,sett_flag,reason
  from settle_order_detail_795 a  
    where a.month_id=201711
and exists(SELECT 1 from serv_mon_795_disconnect b,TB_CRMDB_CUST_201711 c
      where a.cust_id=c.cust_id and c.certi_number=b.certi_number
       and months_between( to_date('201711','yyyymm'),to_date(b.month_id,'yyyymm') ) = any(0,1,2))
and a.prd_id = 80000045
and a.month_id=substr(a.prd_completed_date,0,6)
and a.sett_flag<>3
and not exists (select 1 from tb_pty_agent_ofr_type_rel e
where a.cur_ofr_id = e.ofr_id  and e.ref_dem_type_id = any (1049,1050,1057,1058) )
and not exists (SELECT 1 from serv_mon_795_201711 d
where a.prd_inst_id = d.serv_id and d.payment_mode = 1 )



怎么修改SQL，让它最后才去过滤  not exists (SELECT 1 from serv_mon_795_201711 d
where a.prd_inst_id = d.serv_id and d.payment_mode = 1 ) ；因为前面所有的执行都很快，执行出来的结果集也就100多条数据，但一加上最后那个 not exists 语句就好慢。
尝试写了 /*+leading(a,c,b)*/ 仍然没效果。

Naldonado

两个计划发出来

sqysl

各表多少数据？各子查询返回多少数据？

newkid

既然已经知道去掉这个条件会很快而且结果很小，那么就用 WITH tmp_data as (SELECT /*+ MATERIALIZE */ .... --- 去掉NOT EXIST的其他部分)
然后在此基础上再加上NOT EXISTS条件。

sqysl

试试这个呢：
SELECT /*+ no_push_subq(@subq1)*/ billing_cycle_id,month_id,settle_detail_id,cust_id,section_id,order_finish_date,calc_charge,prd_inst_id,service_nbr,prd_id,prd_completed_date,real_sett_cycle_id,sett_flag,reason
  from settle_order_detail_795 a  
    where a.month_id=201711
and exists(SELECT 1 from serv_mon_795_disconnect b,TB_CRMDB_CUST_201711 c
      where a.cust_id=c.cust_id and c.certi_number=b.certi_number
       and months_between( to_date('201711','yyyymm'),to_date(b.month_id,'yyyymm') ) = any(0,1,2))
and a.prd_id = 80000045
and a.month_id=substr(a.prd_completed_date,0,6)
and a.sett_flag<>3
and not exists (select 1 from tb_pty_agent_ofr_type_rel e
where a.cur_ofr_id = e.ofr_id  and e.ref_dem_type_id = any (1049,1050,1057,1058) )
and not exists (SELECT /*+ no_unnest qb_name(subq1)*/ 1 from serv_mon_795_201711 d
where a.prd_inst_id = d.serv_id and d.payment_mode = 1 )

bowshy

sqysl 发表于 2017-12-5 15:52
试试这个呢：
SELECT /*+ no_push_subq(@subq1)*/ billing_cycle_id,month_id,settle_detail_id,cust_id,s ...

谢谢。可行。
很快了。

bowshy

newkid 发表于 2017-12-4 22:23
既然已经知道去掉这个条件会很快而且结果很小，那么就用 WITH tmp_data as (SELECT /*+ MATERIALIZE */ ... ...

谢谢，你的方法更简便。可行。

richto

Try this product, you will get a solution automatically: