我有一个sql:
select t.carr_cd 航空公司,
count(distinct(t.day_id || t.carr_cd ||trim(t.flt_nbr||t.flt_nbr_sfx))) 班次,
SUM(t.Pax_Qty) 旅客量,
SUM(t.grp_pax_qty) 团队旅客量,
SUM(case
when t.Flt_Seg_Seq_Nbr = '23' or t.Flt_Seg_Seq_Nbr = '12' then
t.Cls_Cpc_Qty
else
0
end) 投入布局数,
round(SUM(t.rvn_amt_tk), 2) 销售收入,
SUM(t.pax_x_dstnc_b),
SUM(t.cpc_x_dstnc_b),
round(SUM(t.rvn_amt_b_tk), 2)
from DSS_PMART.SFLT_AL_SEG_DAILY_SUM_RATIO t
where (t.day_id between to_date('20130501', 'yyyymmdd') and
to_date('20130529', 'yyyymmdd')) and t.arrv_airpt_cd='KMG' and t.dpt_cntry_cd='CN'
and (t.dpt_airpt_cd||t.arrv_airpt_cd) in (select
distinct dpt_airpt_cd||arrv_airpt_cd
from dss_pmart.sflt_al_seg_daily_sum_ratio
where (DAY_ID between to_date('20130501','yyyymmdd') AND to_date('20130529','yyyymmdd')) and carr_cd = 'CA' and arrv_airpt_cd='KMG')
group by t.arrv_airpt_cd,t.carr_cd
order by 班次 desc在红色的in查询中,查出的结果是
1 KMGCSX
2 KMGCTU
3 KMGHGH
4 KMGPEK
5 KMGRGN
6 KMGSHA
7 KMGTSN
8 KMGWNZ
9 KMGYCU
而且查询很快,大概1秒左右,而且我把红色的sql替换成字符串in('KMGCSX','KMGCTU','KMGHGH',......),查询速度也非常快,但执行原来这个SQL,速度就慢的不行,请问会是什么问题啊?需要如何优化呢,求教,谢谢大家
select t.carr_cd 航空公司,
count(distinct(t.day_id || t.carr_cd ||trim(t.flt_nbr||t.flt_nbr_sfx))) 班次,
SUM(t.Pax_Qty) 旅客量,
SUM(t.grp_pax_qty) 团队旅客量,
SUM(case
when t.Flt_Seg_Seq_Nbr = '23' or t.Flt_Seg_Seq_Nbr = '12' then
t.Cls_Cpc_Qty
else
0
end) 投入布局数,
round(SUM(t.rvn_amt_tk), 2) 销售收入,
SUM(t.pax_x_dstnc_b),
SUM(t.cpc_x_dstnc_b),
round(SUM(t.rvn_amt_b_tk), 2)
from DSS_PMART.SFLT_AL_SEG_DAILY_SUM_RATIO t
where (t.day_id between to_date('20130501', 'yyyymmdd') and
to_date('20130529', 'yyyymmdd')) and t.arrv_airpt_cd='KMG' and t.dpt_cntry_cd='CN'
and (t.dpt_airpt_cd||t.arrv_airpt_cd) in (select
distinct dpt_airpt_cd||arrv_airpt_cd
from dss_pmart.sflt_al_seg_daily_sum_ratio
where (DAY_ID between to_date('20130501','yyyymmdd') AND to_date('20130529','yyyymmdd')) and carr_cd = 'CA' and arrv_airpt_cd='KMG')
group by t.arrv_airpt_cd,t.carr_cd
order by 班次 desc在红色的in查询中,查出的结果是
1 KMGCSX
2 KMGCTU
3 KMGHGH
4 KMGPEK
5 KMGRGN
6 KMGSHA
7 KMGTSN
8 KMGWNZ
9 KMGYCU
而且查询很快,大概1秒左右,而且我把红色的sql替换成字符串in('KMGCSX','KMGCTU','KMGHGH',......),查询速度也非常快,但执行原来这个SQL,速度就慢的不行,请问会是什么问题啊?需要如何优化呢,求教,谢谢大家
from DSS_PMART.SFLT_AL_SEG_DAILY_SUM_RATIO t,
(select distinct dpt_airpt_cd || arrv_airpt_cd dpt_airpt_cd
from dss_pmart.sflt_al_seg_daily_sum_ratio
where (DAY_ID between to_date('20130501', 'yyyymmdd') AND
to_date('20130529', 'yyyymmdd'))
and carr_cd = 'CA'
and arrv_airpt_cd = 'KMG') t1
where (t.day_id between to_date('20130501', 'yyyymmdd') and
to_date('20130529', 'yyyymmdd'))
and t.arrv_airpt_cd = 'KMG'
and t.dpt_cntry_cd = 'CN'
and t.dpt_airpt_cd || t.arrv_airpt_cd =t1.dpt_airpt_cd
group by t.arrv_airpt_cd, t.carr_cd
order by 班次 desc
count(distinct(t.day_id || t.carr_cd ||
trim(t.flt_nbr || t.flt_nbr_sfx))) 班次,
SUM(t.Pax_Qty) 旅客量,
SUM(t.grp_pax_qty) 团队旅客量,
SUM(case
when t.Flt_Seg_Seq_Nbr = '23' or t.Flt_Seg_Seq_Nbr = '12' then
t.Cls_Cpc_Qty
else
0
end) 投入布局数,
round(SUM(t.rvn_amt_tk), 2) 销售收入,
SUM(t.pax_x_dstnc_b),
SUM(t.cpc_x_dstnc_b),
round(SUM(t.rvn_amt_b_tk), 2)
from DSS_PMART.SFLT_AL_SEG_DAILY_SUM_RATIO t
where exists (select 1
from dss_pmart.sflt_al_seg_daily_sum_ratio t2
where (t2.DAY_ID between to_date('20130501', 'yyyymmdd') AND
to_date('20130529', 'yyyymmdd'))
and t2.carr_cd = 'CA'
and t2.arrv_airpt_cd = 'KMG'
and t.dpt_airpt_cd = t2.dpt_airpt_cd
and t.arrv_airpt_cd = t2.arrv_airpt_cd)
and t.arrv_airpt_cd = 'KMG'
and t.dpt_cntry_cd = 'CN'
and (t.day_id between to_date('20130501', 'yyyymmdd') and
to_date('20130529', 'yyyymmdd'))
group by t.arrv_airpt_cd, t.carr_cd
order by t.day_id desc, t.carr_cd desc, t.flt_nbr desc, t.flt_nbr_sfx desc
distinct dpt_airpt_cd||arrv_airpt_cd
from dss_pmart.sflt_al_seg_daily_sum_ratio
where (DAY_ID between to_date('20130501','yyyymmdd') AND to_date('20130529','yyyymmdd')) and carr_cd = 'CA' and arrv_airpt_cd='KMG') 的时候肯定是两表关联做筛选,而用in查询结果的时候扫描一个表肯定会快很多优化的话建议建立下面的索引试试看:
create index DSS_PMART.SFLT_AL_SEG_DAILY_SUM_RATIO_1 on DSS_PMART.SFLT_AL_SEG_DAILY_SUM_RATIO(dpt_airpt_cd||arrv_airpt_cd) nologging;