使用WITH语法优化SQL一则

最新推荐文章于 2024-07-19 23:18:13 发布

老虎刘

最新推荐文章于 2024-07-19 23:18:13 发布

阅读量642

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/youyonghu001/article/details/45170683

本文通过一个实际例子展示了如何使用WITH语法优化SQL查询，将原本需要1.1小时执行的查询降低到21秒，性能提升186倍。通过对相同查询的重复部分进行重构，将查询效率显著提高。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

下面这个SQL执行时间需要1.1小时：

selectround(t1.baodan / 10000, 1) "name1",

decode(t2.baodan, 0,

'--',

round(t1.baodan / t2.baodan *100, 2) - 100) || '%' name2,

round(t3.baodan / 10000, 1)"name3",

decode(t4.baodan,

'--',

round(t3.baodan / t4.baodan *100, 2) - 100) || '%' name4,

round(t5.baodan / 10000, 1)"name5"

from (select nvl((d2.baodan - d1.ybaodan), 0)baodan

from (selectnvl(sum(c.total_agree_premium), 0) ybaodan

frompams_auto_tempsave_extend a,apply_base_info c

where ( a.biz_apply_policy_no=c.apply_policy_noor a.force_apply_policy_no=c.apply_policy_no )

and c.created_date <trunc(sysdate - 1)

andtrunc(c.insurance_begin_time) < trunc(sysdate - 1)

and c.policy_no is not null

) d1,

(selectnvl(sum(c.total_agree_premium), 0) baodan

from pams_auto_tempsave_extend a,apply_base_info c

where (a.biz_apply_policy_no=c.apply_policy_no ora.force_apply_policy_no=c.apply_policy_no )

and c.created_date <trunc(sysdate)

andtrunc(c.insurance_begin_time) < trunc(sysdate)

and c.policy_no is not null

) d2

) t1,

(select nvl((d2.baodan - d1.ybaodan), 0)baodan

from (selectnvl(sum(c.total_agree_premium), 0) ybaodan

from pams_auto_tempsave_extend a,apply_base_info c

where (a.biz_apply_policy_no=c.apply_policy_no ora.force_apply_policy_no=c.apply_policy_no )

and c.created_date <trunc(sysdate - 2)

andtrunc(c.insurance_begin_time) < trunc(sysdate - 2)

and c.policy_no is not null)d1,

(selectnvl(sum(c.total_agree_premium), 0) baodan

from pams_auto_tempsave_extend a,apply_base_info c

where ( a.biz_apply_policy_no=c.apply_policy_no ora.force_apply_policy_no=c.apply_policy_no )

and c.created_date <trunc(sysdate - 1)

andtrunc(c.insurance_begin_time) < trunc(sysdate - 1)

and c.policy_no is not null)d2

) t2,

(select nvl((d2.baodan - d1.ybaodan), 0)baodan

from (selectnvl(sum(c.total_agree_premium), 0) ybaodan

from pams_auto_tempsave_extend a,apply_base_info c

where (a.biz_apply_policy_no=c.apply_policy_no ora.force_apply_policy_no=c.apply_policy_no )

and c.created_date <trunc(sysdate - 1, 'month')

andtrunc(c.insurance_begin_time) < trunc(sysdate - 1, 'month')

and c.policy_no is not null)d1,

(selectnvl(sum(c.total_agree_premium), 0) baodan

frompams_auto_tempsave_extend a, apply_base_info c

where (a.biz_apply_policy_no=c.apply_policy_no or a.force_apply_policy_no=c.apply_policy_no)

and c.created_date <trunc(sysdate)

andtrunc(c.insurance_begin_time) < trunc(sysdate)

and c.policy_no is not null)d2

) t3,

(select nvl(sum(c.TOTAL_AGREE_PREMIUM), 0)baodan

from pams_auto_tempsave_extend a,apply_base_info c

where (a.biz_apply_policy_no=c.apply_policy_no ora.force_apply_policy_no=c.apply_policy_no )

and c.created_date betweentrunc(add_months(sysdate - 1, -1), 'month') and trunc(add_months(sysdate, -1))

and trunc(c.insurance_begin_time)< trunc(sysdate)

and c.policy_no is not null

) t4,

(select nvl(sum(c.TOTAL_AGREE_PREMIUM),0) baodan

from pams_auto_tempsave_extend a,apply_base_info c

where (a.biz_apply_policy_no=c.apply_policy_no ora.force_apply_policy_no=c.apply_policy_no )

and c.created_date <trunc(sysdate)

and trunc(c.insurance_begin_time)< trunc(sysdate)

and c.policy_no is not null

) t5;

注意：标红的部分代码都是相同的，执行计划如下：

根据SQL的特点，改写如下：

withtmp_join

as(select c.total_agree_premium,c.created_date,c.insurance_begin_time,c.policy_no

frompams_auto_tempsave_extend a,apply_base_info c

where (a.biz_apply_policy_no=c.apply_policy_no ora.force_apply_policy_no=c.apply_policy_no )

)

selectround(t1.baodan / 10000, 1) "name1",

decode(t2.baodan, 0, '--',

round(t1.baodan / t2.baodan *100, 2) - 100) || '%' name2,

round(t3.baodan / 10000, 1)"name3",

decode(t4.baodan, 0, '--',

round(t3.baodan / t4.baodan *100, 2) - 100) || '%' name4,

round(t5.baodan / 10000, 1)"name5"

from (select nvl((d2.baodan - d1.ybaodan), 0)baodan

from (selectnvl(sum(c.total_agree_premium), 0) ybaodan

from tmp_joinc where

c.created_date <trunc(sysdate - 1)

andtrunc(c.insurance_begin_time) < trunc(sysdate - 1)

and c.policy_no is not null

) d1,

(selectnvl(sum(c.total_agree_premium), 0) baodan

from tmp_joinc where

c.created_date < trunc(sysdate)

andtrunc(c.insurance_begin_time) < trunc(sysdate)

and c.policy_no is not null

) d2

) t1,

(select nvl((d2.baodan - d1.ybaodan), 0)baodan

from (selectnvl(sum(c.total_agree_premium), 0) ybaodan

fromtmp_join c where

c.created_date < trunc(sysdate - 2)

andtrunc(c.insurance_begin_time) < trunc(sysdate - 2)

and c.policy_no is not null) d1,

(selectnvl(sum(c.total_agree_premium), 0) baodan

from tmp_joinc where

c.created_date < trunc(sysdate - 1)

andtrunc(c.insurance_begin_time) < trunc(sysdate - 1)

and c.policy_no is not null)d2

) t2,

(select nvl((d2.baodan - d1.ybaodan), 0)baodan

from (selectnvl(sum(c.total_agree_premium), 0) ybaodan

from tmp_joinc where

c.created_date < trunc(sysdate - 1,'month')

andtrunc(c.insurance_begin_time) < trunc(sysdate - 1, 'month')

and c.policy_no is not null)d1,

(selectnvl(sum(c.total_agree_premium), 0) baodan

fromtmp_join c where

c.created_date < trunc(sysdate)

andtrunc(c.insurance_begin_time) < trunc(sysdate)

and c.policy_no is not null)d2

) t3,

(select nvl(sum(c.TOTAL_AGREE_PREMIUM),0) baodan

from tmp_joinc where

c.created_date betweentrunc(add_months(sysdate - 1, -1), 'month') and trunc(add_months(sysdate, -1))

and trunc(c.insurance_begin_time)< trunc(sysdate)

and c.policy_no is not null

) t4,

(select nvl(sum(c.TOTAL_AGREE_PREMIUM),0) baodan

from tmp_joinc where

c.created_date < trunc(sysdate)

and trunc(c.insurance_begin_time)< trunc(sysdate)

and c.policy_no is not null

) t5;

改写后SQL执行时间21秒，性能提高186倍，执行计划如下：