oracle sql 高级编程学习笔记(二十三)

继上一篇中讲到model子句的性能优化,本文将继续学习model子句查询重写的优化:

一、谓语前推

1、实例演示:

select * from (select product, country, year, week, inventory, sale, receipts
  from sales_fact
     model return updated rows 
   partition by(product, country)
   dimension by(year, week)
   measures(0 inventory, sale, receipts) 
   rules automatic order
   (inventory [ year, week ] order by year,week asc   
    = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]
   )  
 order by product, country, year, week)
 where 
--把谓词放到视图外面,看执行计划是否能进行谓词推进
         country = 'Australia'
   and product = 'Xtend Memory';

这里写图片描述
很明显第四步中两个谓词都推进了视图中,先进行了谓词筛选,然后在结果集中应用了model子句,
这样相对于model子句是非常小的数据行集

2 、没有进行谓词推进

我们把上一步中的谓词改为year=’2000’


select * from (select product, country, year, week, inventory, sale, receipts
  from sales_fact
     model return updated rows 
   partition by(product, country)
   dimension by(year, week)
   measures(0 inventory, sale, receipts) 
   rules automatic order
   (inventory [ year, week ] order by year,week asc   
    = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]
   )  
 order by product, country, year, week)
 where   year='2000';

这里写图片描述
第四部中并没有进行谓词筛选,二十全表扫描得到111k行数据,model子句需要处理这些数据后
再进行谓词筛选

二、物化视图重写

一般来说分区列上的的谓词都可以安全地推进到视图中,但并不是所有维度列上的谓词都可以进行推进;
–创建物化视图

create materialized view mv_model_inventory 
 enable query  rewrite as
--可以查询重写
 select product, country, year, week, inventory, sale, receipts
  from sales_fact
     model return updated rows 
   partition by(product, country)
   dimension by(year, week)
   measures(0 inventory, sale, receipts) 
   rules automatic order
   (inventory [ year, week ] order by year,week asc   
    = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]
   )  ;

查看查询:

select * from (select product, country, year, week, inventory, sale, receipts
  from sales_fact
     model return updated rows 
   partition by(product, country)
   dimension by(year, week)
   measures(0 inventory, sale, receipts) 
   rules automatic order
   (inventory [ year, week ] order by year,week asc   
    = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]
   )  
  )
 where country = 'Australia'
   and product = 'Xtend Memory';

这里写图片描述

可以看到sql访问的是刚刚创建的物化视图,而不是sales_fact表,这样重写提高了sql语句的性能,因为物化视图
对规则进行了预求解并存储了结果

注意:包含model子句的物化视图不适用快速增量刷新
model 子句与oracle的并行执行能力实现无缝结合,并行和基于model的sql语句可以改善
分区表的查询性能。

三、并行

select /*+parallel(sf,4)*/product, country, year, week, inventory, sale, receipts
  from sales_fact sf
  where country = 'Australia'
   and product = 'Xtend Memory'
     model return updated rows 
   partition by(product, country)
   dimension by(year, week)
   measures(0 inventory, sale, receipts) 
   rules automatic order
   (  inventory [ year, week ] order by year,week asc   
    = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]
   );

这里写图片描述

执行计划中可以看到 优化器分配了两组并行执行机来执行所列出的语句
第一组进行表读取,第二组进行model规则求解。

四、分区

重建表并按时create table SALES_FACT
(
country VARCHAR2(40) not null,
region VARCHAR2(30) not null,
product VARCHAR2(50) not null,
year NUMBER(4) not null,
week NUMBER(2) not null,
sale NUMBER,
receipts NUMBER
)
PARTITION BY LIST (year)
(
PARTITION SALES_FACT_PART_1998 VALUES (1998) ,
PARTITION SALES_FACT_PART_1999 VALUES (1999) ,
PARTITION SALES_FACT_PART_2000 VALUES (2000) ,
PARTITION SALES_FACT_PART_2001 VALUES (2001)
);分区

select * from (select year, product, country , week, inventory, sale, receipts
  from  SALES_FACT 
  where country = 'Australia'
   and product = 'Xtend Memory'
     model return updated rows 
   partition by(year, country)
--这里分区只能把year 加上
--维度列 不能再是year,不然会报列重复错误
   dimension by(product, week)
   measures(0 inventory, sale, receipts) 
   rules automatic order
   (  inventory [ product, week ] order by product,week asc   
    = nvl(inventory [ cv(product), cv(week) - 1 ], 0) - sale [ cv(product), cv(week) ] + receipts [ cv(product), cv(week) ]
   ))
   where   country = 'Australia'
   and product = 'Xtend Memory'
   and year=2000;
   ;

这里写图片描述
看到分区裁剪使得只分文分区3 Pstart 3 Pstop 3表示处理的分区范围开始和结束都是id=3的单个分区, 这是由model子句
作为分区列并且指定了year=2000谓语。还能看到的是执行计划中的谓词并没有year:=2000
select * from (select year, product, country , week, inventory, sale, receipts
from SALES_FACT
where country = ‘Australia’
and product = ‘Xtend Memory’
model return updated rows
partition by(product, country)
dimension by(year, week)
measures(0 inventory, sale, receipts)
rules automatic order
( inventory [ year, week ] order by year,week asc
= nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]
))
where year=2000 and country = ‘Australia’
and product = ‘Xtend Memory’ ;
这里写图片描述
可以看到执行计划扫描分区范围是从1到4 等于是全表扫描,并且再最后一步中再进行谓词year=2000的筛选
year 是维度列,并没有推进到最开始的表扫描中,虽然year是表sales_fact的分区键,但他没有推进视图中,就不能进行分区裁剪
只能进行全表扫描。

当然也可以指定访问的分区

select * from (select year, product, country , week, inventory, sale, receipts
  from  SALES_FACT  partition (SALES_FACT_PART_2000)
--指定访问year=2000的分区
  where country = 'Australia'
   and product = 'Xtend Memory'
     model return updated rows 
   partition by(product, country)
   dimension by(year, week)
   measures(0 inventory, sale, receipts) 
   rules automatic order
   (  inventory [ year, week ] order by year,week asc   
    = nvl(inventory [ cv(year), cv(week) - 1 ], 0) - sale [ cv(year), cv(week) ] + receipts [ cv(year), cv(week) ]
   ))
   where   year=2000 and  country = 'Australia'
   and product = 'Xtend Memory' ;

这里写图片描述
可以看到访问的分区范围只是id=3这个分区,但不同的是year因为是维度列,还是不能推进视图中。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

菜菜的中年程序猿

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值