物化视图
--物化视图与查询重写
DROP MATERIALIZED VIEW mv_model_inventory;
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 sequential order(
inventory[year,week] order by year, week=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 sequential order(
inventory[year,week] order by year, week=nvl(inventory[cv(year),cv(week)-1],0)-sale[cv(year),cv(week)]+receipts[cv(year),cv(week)]
)
)
WHERE country IN ('Australia') AND product='Xtend Memory'
ORDER BY product,country,year,week;
执行计划
----------------------------------------------------------
Plan hash value: 2344724570
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 10944 | 278 (2)| 00:00:04 |
| 1 | SORT ORDER BY | | 96 | 10944 | 278 (2)| 00:00:04 |
|* 2 | MAT_VIEW REWRITE ACCESS FULL| MV_MODEL_INVENTORY | 96 | 10944 | 277 (2)| 00:00:04 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MV_MODEL_INVENTORY"."COUNTRY"='Australia' AND
"MV_MODEL_INVENTORY"."PRODUCT"='Xtend Memory')
并行
基于model的sql语句可以与oracle的并行执行能力无缝结合。并行和基于model的sql语句可以改善分区表的查询性能。
SELECT /*+ parallel ( sf 4) */
product,country, year,week,inventory,sale,receipts
from sales_fact
WHERE country IN ('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=nvl(inventory[cv(year),cv(week)-1],0)-sale[cv(year),cv(week)]+receipts[cv(year),cv(week)]
)
model子句执行中的分区
分区表可以改进model sql语句的性能。如果model sql中的分区列与表的分区键相匹配，分区就是被剪裁过的。分区裁剪是一门将扫描限制在较少分区中的性能优化技术。
--分区剪裁
SELECT *
FROM (SELECT product, country, YEAR, week, inventory, sale, receipts
FROM sales_fact sf
model RETURN updated rows
PARTITION BY(YEAR, country)
dimension BY(product, week)
measures(0 inventory, sale, receipts)
rules AUTOMATIC ORDER(
inventory [ product, week ] ORDER BY product, week =
nvl(inventory [ cv(product), cv(week) - 1 ], 0) - sale [ cv(product),
cv(week) ] + +receipts [ cv(product),
cv(week) ]))
WHERE YEAR = 2000
AND country = 'Australia'
AND product = 'Xtend Memory'
--不进行分区剪裁
SELECT * FROM (
SELECT product, country, year, week, inventory, sale, receipts
FROM sales_fact sf
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 =
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'
执行计划
----------------------------------------------------------
Plan hash value: 463616151
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 154 | 17556 | 310 (1)| 00:00:04 |
|* 1 | VIEW | | 154 | 17556 | 310 (1)| 00:00:04 |
| 2 | SQL MODEL ACYCLIC | | 154 | 8624 | | |
|* 3 | TABLE ACCESS FULL| SALES_FACT | 154 | 8624 | 310 (1)| 00:00:04 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("YEAR"=2000)
3 - filter("PRODUCT"='Xtend Memory' AND "COUNTRY"='Australia')
product列和country列作为分区列，但表使用year列作为分区键，执行计划中的第１步表明为证year=2000由于规则可能访问其它分区而没有推进到视图中，因为year是一个维度列。因为分区键没有前推进视图中，就不允许进行分区剪裁，从而需要扫描所有分区。
索引
选择索引来提高使用model子句的sql语句性能与选择索引提高其它的任何sql语句的性能都是一样的。可以使用访问和筛选谓语确定最佳的索引策略。
--记住sql数据访问索引
SELECT * FROM (
SELECT product, country, year, week, inventory, sale, receipts
FROM sales_fact sf
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 =
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'
执行计划
----------------------------------------------------------
Plan hash value: 918126316
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 154 | 17556 | 139(0)| 00:00:02 |
|* 1 | VIEW | | 154 | 17556 | 139(0)| 00:00:02 |
| 2 | SQL MODEL ACYCLIC | | 154 | 8624 | | |
| 3 | TABLE ACCESS BY INDEX ROWID| SALES_FACT | 154 | 8624 | 139(0)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | SALES_FACT_I1 | 154 | | 4(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("YEAR"=2000)
4 - access("COUNTRY"='Australia' AND "PRODUCT"='Xtend Memory')
子查询因子
在商务环境中，需求是很复杂的并且经常需要多个层级的聚合。当书写复杂查询时，将子查询因子化与model子句结合起来，以避免sql语句变得复杂而不易管理。
--记住更多sql数据访问索引的内容
WITH t1 AS (
SELECT product, country, year, week, inventory, sale, receipts
FROM sales_fact sf
WHERE country IN ('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 =
nvl(inventory [ cv(year), cv(week) - 1 ], 0)
-sale [ cv(year), cv(week) ]
+receipts [ cv(year),cv(week) ])
)
SELECT product, country, year, week, inventory, sale, receipts, prev_sale
FROM t1
MODEL RETURN UPDATED ROWS
PARTITION BY(product,country)
DIMENSION BY(year,week)
MEASURES (inventory,sale,receipts,0 prev_sale)
RULES SEQUENTIAL ORDER(
prev_sale[year,week] ORDER BY year,week =
NVL(sale[cv(year)-1,cv(week)],0)
)
ORDER BY 1,2,3,4;
小结
写sql语句以集合的思维方式思考，很多sql语句都可以使用model子句来简洁地进行重写。用model子句或分析函数进行查询重写可以获取比传统的sql语句好得多的性能。子查询因子化，model和分析函数特性的结合可以用来有效实现复杂的需求。
