求修改一条sql

Question

 with tmp as(select orgcode,orgname from from jjxxb 
start with orgcode='91100001' connect by prior ORGID = PARENTORGID  ),                               
tm as         ( 
select t.rpt_date,
       t.inst_no,
       tmp.orgname,
       t.inst_type,
       t.svt_no,
       t.bal_month,
       t.bal_season,
       t.bal_year
  from money_ave t, tmp
 where t.inst_no = tmp.orgcode
   and (total_flag = '2' or total_flag = '3')), 
hqtm as (
select tm.rpt_date,
       tm.inst_no,
       tm.orgname,
       tm.inst_type,
       tm.svt_no,
       tm.bal_month,
       tm.bal_season,
       tm.bal_year
  from tm
 where tm.svt_no in (02, 03, 04)),
tztm as (
select tm.rpt_date,
       tm.inst_no,
       tm.orgname,
       tm.inst_type,
       tm.svt_no,
       tm.bal_month,
       tm.bal_season,
       tm.bal_year
  from tm
 where tm.svt_no in (92, 93, 94)),
dqtm as (
select tm.rpt_date,
       tm.inst_no,
       tm.orgname,
       tm.inst_type,
       tm.svt_no,
       tm.bal_month,
       tm.bal_season,
       tm.bal_year
  from tm
 where tm.svt_no not in (02, 03, 04, 92, 93, 94))  select dqtm.inst_no orgcode,
         dqtm.orgname orgname,
         sum(decode(trim(hqtm.rpt_date), '200804', hqtm.bal_month, 0)) hqthis,
         sum(decode(trim(hqtm.rpt_date), '200803', hqtm.bal_month, 0)) hqlm,
         sum(decode(trim(hqtm.rpt_date), '200804', hqtm.bal_season, 0)) hqthiss,
         sum(decode(trim(hqtm.rpt_date), '200803', hqtm.bal_season, 0)) hqls,
         sum(decode(trim(hqtm.rpt_date), '200804', hqtm.bal_year, 0)) hqthisy,
         sum(decode(trim(hqtm.rpt_date), '200712', hqtm.bal_year, 0)) hqly,
         sum(decode(trim(tztm.rpt_date), '200804', tztm.bal_month, 0)) tzthis,
         sum(decode(trim(tztm.rpt_date), '200803', tztm.bal_month, 0)) tzlm,
         sum(decode(trim(tztm.rpt_date), '200804', tztm.bal_season, 0)) tzthiss,
         sum(decode(trim(tztm.rpt_date), '200803', tztm.bal_season, 0)) tzls,
         sum(decode(trim(tztm.rpt_date), '200804', tztm.bal_year, 0)) tzthisy,
         sum(decode(trim(tztm.rpt_date), '200712', tztm.bal_year, 0)) tzly,
         sum(decode(trim(dqtm.rpt_date), '200804', dqtm.bal_month, 0)) dqthis,
         sum(decode(trim(dqtm.rpt_date), '200803', dqtm.bal_month, 0)) dqlm,
         sum(decode(trim(dqtm.rpt_date), '200804', dqtm.bal_season, 0)) dqthiss,
         sum(decode(trim(dqtm.rpt_date), '200803', dqtm.bal_season, 0)) dqls,
         sum(decode(trim(dqtm.rpt_date), '200804', dqtm.bal_year, 0)) dqthisy,
         sum(decode(trim(dqtm.rpt_date), '200712', dqtm.bal_year, 0)) dqly
    from hqtm, tztm, dqtm
   where hqtm.inst_no = dqtm.inst_no
     and tztm.inst_no = dqtm.inst_no
   group by dqtm.inst_no, dqtm.orgname
order by dqtm.inst_no求这条sql要怎么优化呢？这个表money_ave数据挺大的，跑这条sql很慢

Answer 1

tm as         ( 
select t.rpt_date,
       t.inst_no,
       tmp.orgname,
       t.inst_type,
       t.svt_no,
       t.bal_month,
       t.bal_season,
       t.bal_year
  from money_ave t, tmp
 where t.inst_no = tmp.orgcode
   and (total_flag = '2' or total_flag = '3')), 
将上面查询的两个表from money_ave t, tmp  交换下顺序吧， 让tmp 表作为基表试试，不然money_ave表数据大，这样money_ave就作为基表了，然后扫描大数据的表就慢了小弟拙见。

Answer 2

在inst_no上建索引
给total_flag建位图索引

Answer 3

where tm.svt_no in (02, 03, 04))where tm.svt_no in (92, 93, 94))where tm.svt_no not in (02, 03, 04, 92, 93, 94))真不知道你这是要干什么，这么的矛盾。要快点在建联合索引dqtm.inst_no, dqtm.orgname 把dqtm.inst_no这个为索引首字段 试试看

Answer 4

with tmp as(select orgcode,orgname from from jjxxb 
start with orgcode='91100001' connect by prior ORGID = PARENTORGID  ),                               
tm as         ( 
select t.rpt_date,
       t.inst_no,
       tmp.orgname,
       t.inst_type,
       t.svt_no,
       t.bal_month,
       t.bal_season,
       t.bal_year
  from money_ave t, tmp
 where t.inst_no = tmp.orgcode
   and (total_flag = '2' or total_flag = '3'))
  select dqtm.inst_no orgcode,
         dqtm.orgname orgname,
         SUM(CASE WHEN svt_no in (02, 03, 04) AND TRIM(rpt_date)='200804' THEN bal_month ELSE 0)  hqthis 
         SUM(CASE WHEN svt_no in (02, 03, 04) AND TRIM(rpt_date)='200803' THEN bal_month ELSE 0)  hqlm
         SUM(CASE WHEN svt_no in (02, 03, 04) AND TRIM(rpt_date)='200804' THEN bal_season ELSE 0)  hqthiss 
         SUM(CASE WHEN svt_no in (02, 03, 04) AND TRIM(rpt_date)='200803' THEN bal_season ELSE 0)  hqls
         SUM(CASE WHEN svt_no in (02, 03, 04) AND TRIM(rpt_date)='200804' THEN bal_year ELSE 0)  hqthisy 
         SUM(CASE WHEN svt_no in (02, 03, 04) AND TRIM(rpt_date)='200803' THEN bal_year ELSE 0)  hqly
         SUM(CASE WHEN svt_no in (92, 93, 94) AND TRIM(rpt_date)='200804' THEN bal_month ELSE 0)  tzthis 
         SUM(CASE WHEN svt_no in (92, 93, 94) AND TRIM(rpt_date)='200803' THEN bal_month ELSE 0)  tzlm
         SUM(CASE WHEN svt_no in (92, 93, 94) AND TRIM(rpt_date)='200804' THEN bal_season ELSE 0)  tzthiss 
         SUM(CASE WHEN svt_no in (92, 93, 94) AND TRIM(rpt_date)='200803' THEN bal_season ELSE 0)  tzls
         SUM(CASE WHEN svt_no in (92, 93, 94) AND TRIM(rpt_date)='200804' THEN bal_year ELSE 0)  tzthisy 
         SUM(CASE WHEN svt_no in (92, 93, 94) AND TRIM(rpt_date)='200803' THEN bal_year ELSE 0)  tzly          
         SUM(CASE WHEN svt_no NOT in (02, 03, 04,92, 93, 94) AND TRIM(rpt_date)='200804' THEN bal_month ELSE 0)  dqthis 
         SUM(CASE WHEN svt_no NOT in (02, 03, 04,92, 93, 94) AND TRIM(rpt_date)='200803' THEN bal_month ELSE 0)  dqlm
         SUM(CASE WHEN svt_no NOT in (02, 03, 04,92, 93, 94) AND TRIM(rpt_date)='200804' THEN bal_season ELSE 0)  dqthiss 
         SUM(CASE WHEN svt_no NOT in (02, 03, 04,92, 93, 94) AND TRIM(rpt_date)='200803' THEN bal_season ELSE 0)  dqls
         SUM(CASE WHEN svt_no NOT in (02, 03, 04,92, 93, 94) AND TRIM(rpt_date)='200804' THEN bal_year ELSE 0)  dqthisy 
         SUM(CASE WHEN svt_no NOT in (02, 03, 04,92, 93, 94) AND TRIM(rpt_date)='200803' THEN bal_year ELSE 0)  dqly    from tm

Answer 5

1.重复的sum操作太多应该合并with tmp as(select orgcode,orgname from from jjxxb 
start with orgcode='91100001' connect by prior ORGID = PARENTORGID  ), 
tm as (
select distinct 
       t.rpt_date,
       t.inst_no,
       tmp.orgname,
       t.svt_no,
       sum(t.bal_month)  over(partition by t.inst_no,tmp.orgname,t.svt_no) as sum_bal_month,
       sum(t.bal_season) over(partition by t.inst_no,tmp.orgname,t.svt_no) as sum_bal_season,
       sum(t.bal_year)   over(partition by t.inst_no,tmp.orgname,t.svt_no)as sum_bal_year
  from 
(
select 
t.inst_no,
t.total_flag,
t.rpt_date,
case when t.svt_no='02' or t.svt_no='03'or t.svt_no='04' then 'hqtm_svtno'
     when t.svt_no='92' or t.svt_no='93'or t.svt_no='94' then 'tztm_svtno'
     else 'dqtm_svtno' end as svt_no,
t.bal_month,
t.bal_season,
t.bal_year
from money_ave
)t, tmp
 where t.inst_no = tmp.orgcode
   and t.total_flag in('2','3')
)
select   inst_no orgcode,
         orgname orgname,
         decode(trim(rpt_date), '200804', sum_bal_month, 0) hqthis,
         decode(trim(rpt_date), '200803', sum_bal_month, 0)  hqlm,
 decode(trim(rpt_date), '200804', sum_bal_season, 0) hqthiss,
 decode(trim(rpt_date), '200803', sum_bal_season, 0) hqls,
 decode(trim(rpt_date), '200804', sum_bal_year, 0) hqthisy,
 decode(trim(rpt_date), '200712', sum_bal_year, 0) hqly
from tm
where svt_no = 'hqtm_svtno'
union all
select   inst_no orgcode,
         orgname orgname,
         decode(trim(rpt_date), '200804', sum_bal_month, 0) tzthis,
         decode(trim(rpt_date), '200803', sum_bal_month, 0)  tzlm,
 decode(trim(rpt_date), '200804', sum_bal_season, 0) tzthiss,
 decode(trim(rpt_date), '200803', sum_bal_season, 0) tzls,
 decode(trim(rpt_date), '200804', sum_bal_year, 0) tzthisy,
 decode(trim(rpt_date), '200712', sum_bal_year, 0) tzly
from tm
where svt_no = 'tztm_svtno'
union all
select   inst_no orgcode,
         orgname orgname,
         decode(trim(rpt_date), '200804', sum_bal_month, 0) dqthis,
         decode(trim(rpt_date), '200803', sum_bal_month, 0)  dqlm,
 decode(trim(rpt_date), '200804', sum_bal_season, 0) dqthiss,
 decode(trim(rpt_date), '200803', sum_bal_season, 0) dqls,
 decode(trim(rpt_date), '200804', sum_bal_year, 0) dqthisy,
 decode(trim(rpt_date), '200712', sum_bal_year, 0) dqly
from tm
where svt_no = 'dqtm_svtno';2.如果可能的话..将tm的结果保存在一个临时表中例如create table tm_table nologging
as
select ...
  from money_ave t, tmp
 where ...