ORACLE WITH AS 临时表的使用方法

今天写了个语句,要对同一张表不同的字段分别查询并汇总,整个嵌套了5次。执行至少要3分钟,去网上找优化办法,才发现原来还有个with as 建立临时表的解决办法,只用查询一次就可以了,优秀啊。


WITH Clause方法的优点

     增加了SQL的易读性,如果构造了多个子查询,结构会更清晰;更重要的是:“一次分析,多次使用”,这也是为什么会提供性能的地方,达到了“少读”的目标。

     第一种使用子查询的方法表被扫描了两次,而使用WITH Clause方法,表仅被扫描一次。这样可以大大的提高数据分析和查询的效率。

     另外,观察WITH Clause方法执行计划,其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。


语法结构:

with tempName as (select ....)
select ...

--针对一个别名with tmp as (select * from tb_name)--针对多个别名with
   tmp as (select * from tb_name),
   tmp2 as (select * from tb_name2),
   tmp3 as (select * from tb_name3),
   …
   
--相当于建了个e临时表with e as (select * from scott.emp e where e.empno=7499)select * from e; 
--相当于建了e、d临时表with
     e as (select * from scott.emp),
     d as (select * from scott.dept)select * from e, d where e.deptno = d.deptno;



实例

调整前

select
distinct  l.wave_nbr "波次号",
l.TC_order_id  as  配送订单,
PH.D_FACILITY_NAME as 门店名称,
lh2.locn_brcd as 集货位,
count(distinct b.lpn_id) 标准箱数,
count(distinct f.lpn_id) 辅料箱数,
count(distinct x.lpn_id) 鞋品箱数,
count(distinct c.lpn_id) 非标箱数
from
lpn l
right join locn_hdr lh2
on l.dest_sub_locn_id=lh2.locn_id
left JOIN ORDERS PH ON l.TC_ORDER_ID = PH. TC_ORDER_ID
inner join (
select
l.TC_order_id  as  配送订单,
l.tc_lpn_id as lpn_id
from
lpn l
inner join locn_hdr lh on l.pick_sub_locn_id = lh.locn_id
where
l.inbound_outbound_indicator = 'O'
   and l.lpn_facility_status < 90
   and (substr(lh.zone,-1,1)='B' or substr(lh.zone,-1,1)='D')
) b on  l.TC_order_id=b.配送订单
left join (
select
l.TC_order_id  as  配送订单,
l.tc_lpn_id as lpn_id
from
lpn l
inner join locn_hdr lh on l.pick_sub_locn_id = lh.locn_id
where
l.inbound_outbound_indicator = 'O'
   and l.lpn_facility_status < 90
   and substr(lh.zone,-1,1)='F'
   
) f on l.TC_order_id=f.配送订单
left join (
select
l.TC_order_id  as  配送订单,
l.tc_lpn_id as lpn_id
from
lpn l
inner join locn_hdr lh on l.pick_sub_locn_id = lh.locn_id
where
l.inbound_outbound_indicator = 'O'
   and l.lpn_facility_status < 90
   and substr(lh.zone,-1,1)='C'
) c on l.TC_order_id=c.配送订单
left join (
select
l.TC_order_id  as  配送订单,
l.tc_lpn_id as lpn_id
from
lpn l
inner join locn_hdr lh on l.pick_sub_locn_id = lh.locn_id
where
l.inbound_outbound_indicator = 'O'
   and l.lpn_facility_status < 90
   and substr(lh.zone,-1,1)='X'
) x on l.TC_order_id=x.配送订单
WHERE
l.wave_nbr='201811270001'
group by l.wave_nbr, PH.D_FACILITY_NAME,l.tc_lpn_id,l.TC_order_id,lh2.locn_brcd

执行时间 3分57 秒


调整后

with ch as(
select
TC_order_id,
tc_lpn_id,
substr(lh.zone,-1,1) as x
from
lpn l
inner join locn_hdr lh on l.pick_sub_locn_id = lh.locn_id
where
l.inbound_outbound_indicator = 'O'
   and l.lpn_facility_status < 90
)
select
distinct  l.wave_nbr "波次号",
l.TC_order_id  as  配送订单,
PH.D_FACILITY_NAME as 门店名称,
lh2.locn_brcd as 集货位,
count(distinct b.lpn_id) 标准箱数,
count(distinct f.lpn_id) 辅料箱数,
count(distinct x.lpn_id) 鞋品箱数,
count(distinct c.lpn_id) 非标箱数
from
lpn l
right join locn_hdr lh2
on l.dest_sub_locn_id=lh2.locn_id
left JOIN ORDERS PH ON l.TC_ORDER_ID = PH. TC_ORDER_ID
inner join (
select 
TC_order_id  as  配送订单,
tc_lpn_id as lpn_id
from
ch
where
(x='B' or x='D')
) b on  l.TC_order_id=b.配送订单
left join (
select 
TC_order_id  as  配送订单,
tc_lpn_id as lpn_id
from
ch
where
x='F'
) f on l.TC_order_id=f.配送订单
left join (
select 
TC_order_id  as  配送订单,
tc_lpn_id as lpn_id
from
ch
where
x='C'
) c on l.TC_order_id=c.配送订单
left join (
select 
TC_order_id  as  配送订单,
tc_lpn_id as lpn_id
from
ch
where
x='X'
) x on l.TC_order_id=x.配送订单
WHERE
l.wave_nbr='201811270001'
group by l.wave_nbr, PH.D_FACILITY_NAME,l.tc_lpn_id,l.TC_order_id,lh2.locn_brcd,

执行时间0.3秒


这提升的速度把自己都惊讶到了。


发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

搜索
«   2019年7月   »
1234567
891011121314
15161718192021
22232425262728
293031
最新留言
    标签列表
      友情链接

      Powered By Z-BlogPHP 1.5.2 Zero