首页 > 其他 > 详细

为啥用了临时表后5000秒降到3秒

时间:2020-01-21 11:26:42      阅读:73      评论:0      收藏:0      [点我收藏+]

代码如下: 把cs_thz_1的cs_thz_11用原sql替代,则cs_thz_1要跑5000秒,改造后,3秒,临时表的数据是16W,2019年一直运行时间在20秒内,2020年以后突然升到5000秒,要插入的目标表只有主键,没有分区,不知道什么原因???

改造后:

create table cs_thz_11 as
select * from R_DW_HelpER_RELATION_D
where stat_time= 20200119 and FAM_STATUS=1 and 20200119 between nvl(help_START_DATE,19000101) and nvl(help_END_DATE,29991231)
;

drop table cs_thz_1;
create table cs_thz_1 as
select
20200119 as stat_time --统计日期
,a.helper_id as helper_id --帮扶负责人id
,a.helper_name as helper_name --帮扶负责人名称
,nvl(x.poor_fam,0) as poor_fam --贫困户数
,nvl(x.poor_pop,0) as poor_pop --贫困人口数
,nvl(x.gov_exp_pop,0) as gov_exp_pop --行业比对存疑人数
,nvl(x.gov_exp_item,0) as gov_exp_item --行业比对存疑条数
,nvl(y.hg_exp_pop,0) as hg_exp_pop --手册比对存疑人数
,nvl(y.hg_exp_item,0) as hg_exp_item --手册比对存疑条数
,sysdate as flow_time --流入时间
from TW_HELP_PERSON_D a
left join (---行业存疑
select t.helper_id,count(distinct t.fam_id) as poor_fam
,sum(case when t.rid=1 then t.fam_pop else 0 end) as poor_pop
,count(distinct t.pop_id) as gov_exp_pop
,count(t.pop_id) as gov_exp_item
from (
select row_number() over (partition by a.helper_id,a.fam_id order by 1) rid,a.fam_id,a.helper_id,a.fam_pop,b.pop_id
from cs_thz_11 a
left join (select * from app_dw_identify_exception_d where stat_time=20200119 and IS_SUB_SOLVE = 0) b
on a.fam_id=b.fam_id
) t
group by t.helper_id
) x on a.helper_id=x.helper_id
left join (---手册比对存疑
select a.helper_id,count(distinct b.idcard18) as hg_exp_pop,count(b.idcard18) as hg_exp_item
from cs_thz_11 a
left join (select * from app_dw_compare_exception_d where stat_time=20200119 ) b
on a.fam_id=b.residence_id
group by a.helper_id
) y on a.helper_id=y.helper_id
where a.stat_time=20200119
;

为啥用了临时表后5000秒降到3秒

原文:https://www.cnblogs.com/jiangqingfeng/p/12220777.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!