此SQL如何优化，

Question

select distinct dist.vcDIST_C_NAME as 地区,tp.vcMSCName as MSC名称,sum(tp.iShortCallNumber) 数量,(select  count(a.vcmscname) 总数 from tbMSCCAllRecord_20080829  a where a.vcmscname= tp.VCMSCNAME group by a.vcmscname
) as 总数 from tbMSCShortCallDayreport tp
inner join tbMSC msc on  tp.vcMSCName = msc.vcMSCName
inner join tbDIC_DISTRICT dist on msc.vcDIST_ID=dist.VCDIST_ID
union
select distinct dist.vcDIST_C_NAME as 地区,tp.vcMSCName as MSC名称,sum(tp.iShortCallNumber) 数量,(select  count(a.vcmscname) 总数 from tbMSCCAllRecord_20080830  a where a.vcmscname= tp.VCMSCNAME group by a.vcmscname
) as 总数 from tbMSCShortCallDayreport tp
inner join tbMSC msc on  tp.vcMSCName = msc.vcMSCName
inner join tbDIC_DISTRICT dist on msc.vcDIST_ID=dist.VCDIST_ID
union
如何 优化这个查询 tbMSCCAllRecord_20080829 和 tbMSCCAllRecord_20080830 都是属于百万级的表
这里要统计30个表的情况  上面我只取2个作例子
列vcmscname 为 索引

Answer 1

with a as(
select distinct dist.vcDIST_C_NAME as 地区,tp.vcMSCName as MSC名称,sum(tp.iShortCallNumber) 数量
from tbMSCShortCallDayreport tp
inner join tbMSC msc on  tp.vcMSCName = msc.vcMSCName
inner join tbDIC_DISTRICT dist on msc.vcDIST_ID=dist.VCDIST_ID
)
select a.*,
(select  count(a.vcmscname) 总数 from tbMSCCAllRecord_20080829  a where a.vcmscname= tp.VCMSCNAME group by a.vcmscname
) as 总数 
  from aunion allselect a.*,
(select  count(a.vcmscname) 总数 from tbMSCCAllRecord_20080830  a where a.vcmscname= tp.VCMSCNAME group by a.vcmscname
) as 总数 
  from a

Answer 2

子查询的group by 不要了with a as(
select distinct dist.vcDIST_C_NAME as 地区,tp.vcMSCName as MSC名称,sum(tp.iShortCallNumber) 数量
from tbMSCShortCallDayreport tp
inner join tbMSC msc on  tp.vcMSCName = msc.vcMSCName
inner join tbDIC_DISTRICT dist on msc.vcDIST_ID=dist.VCDIST_ID
)
select a.*,
(select  count(a.vcmscname) 总数 from tbMSCCAllRecord_20080829  a where a.vcmscname= tp.VCMSCNAME 
) as 总数 
  from aunion allselect a.*,
(select  count(a.vcmscname) 总数 from tbMSCCAllRecord_20080830  a where a.vcmscname= tp.VCMSCNAME 
) as 总数 
  from a

Answer 3

最后还能帖出执行计划with a as(
    select dist.vcDIST_C_NAME as 地区,tp.vcMSCName as MSC名称,sum(tp.iShortCallNumber) 数量
    from tbMSCShortCallDayreport tp
    inner join tbMSC msc on  tp.vcMSCName = msc.vcMSCName
    inner join tbDIC_DISTRICT dist on msc.vcDIST_ID=dist.VCDIST_ID
    GROUP BY dist.vcDIST_C_NAME
     ,tp.vcMSCName
)
select a.*,
    (select  count(a.vcmscname) 总数 from tbMSCCAllRecord_20080829  a where a.vcmscname= tp.VCMSCNAME 
    ) as 总数 
  from aunion allselect a.*,
    (select  count(a.vcmscname) 总数 from tbMSCCAllRecord_20080830  a where a.vcmscname= tp.VCMSCNAME 
    ) as 总数 
  from a

Answer 4

  
with a as(
    select dist.vcDIST_C_NAME as 地区,tp.vcMSCName as MSC名称,sum(tp.iShortCallNumber) 数量
    from tbMSCShortCallDayreport tp
    inner join tbMSC msc on  tp.vcMSCName = msc.vcMSCName
    inner join tbDIC_DISTRICT dist on msc.vcDIST_ID=dist.VCDIST_ID
    GROUP BY dist.vcDIST_C_NAME
            ,tp.vcMSCName
)
select a.*,
    (select  count(a.vcmscname) 总数 from tbMSCCAllRecord_20080829  a where a.vcmscname= a.MSC名称 
    ) as 总数 
  from aunion allselect a.*,
    (select  count(a.vcmscname) 总数 from tbMSCCAllRecord_20080830  a where a.vcmscname= a.MSC名称
    ) as 总数 
  from a楼上代码执行没错，平均每个表查询时间0.8s  30个表查下来效果也不是很理想

Answer 5

不过还是感谢 codearts 

Answer 6

可以加个hint,让a给固化了再查询。对下面的30个union all,因为你要访问30张表，似乎不能再优化了with a as(
    select dist.vcDIST_C_NAME as 地区,tp.vcMSCName as MSC名称,sum(tp.iShortCallNumber) 数量
    from tbMSCShortCallDayreport tp
    inner join tbMSC msc on  tp.vcMSCName = msc.vcMSCName
    inner join tbDIC_DISTRICT dist on msc.vcDIST_ID=dist.VCDIST_ID
    where rownum >=1 --加了这里    GROUP BY dist.vcDIST_C_NAME
            ,tp.vcMSCName
)
select a.*,
    (select  count(a.vcmscname) 总数 from tbMSCCAllRecord_20080829  a where a.vcmscname= a.MSC名称 
    ) as 总数 
  from aunion allselect a.*,
    (select  count(a.vcmscname) 总数 from tbMSCCAllRecord_20080830  a where a.vcmscname= a.MSC名称
    ) as 总数 
  from a

Answer 7

1.先建立一张大的视图如v_all，该视图由tbMSCCAllRecord_20080829,tbMSCCAllRecord_20080830等文件拼成;
2.再建一个存贮过程去创建v_all;
3.先做一个JOB，在JOB中执行上存贮过程，每天执行一次；
4.从tbMSCShortCallDayreport和v_all中执行select ......;

Answer 8

1.先建立一张大的视图如v_all，该视图由tbMSCCAllRecord_20080829,tbMSCCAllRecord_20080830...等文件拼成; 
2.建一个存贮过程去创建v_all; 
3.再做一个JOB，在JOB中执行上面的存贮过程，每天执行一次； 
4.在tbMSCShortCallDayreport表上和v_all视图中执行select ......; 

Answer 9

maybe materialized view is better.

Answer 10

最好的办法就是用视图.
  直接查询视图就OK!引用 12 楼 BlueskyWide 的回复:
1.先建立一张大的视图如v_all，该视图由tbMSCCAllRecord_20080829,tbMSCCAllRecord_20080830...等文件拼成; 
2.建一个存贮过程去创建v_all; 
3.再做一个JOB，在JOB中执行上面的存贮过程，每天执行一次； 
4.在tbMSCShortCallDayreport表上和v_all视图中执行select ......;