物化視圖
--物化視圖與查詢重寫
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;
執(zhí)行計劃
----------------------------------------------------------
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的并行執(zhí)行能力無縫結(jié)合。并行和基于model的sql語句可以改善分區(qū)表的查詢性能。
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子句執(zhí)行中的分區(qū)
分區(qū)表可以改進(jìn)model sql語句的性能。如果model sql中的分區(qū)列與表的分區(qū)鍵相匹配使碾，分區(qū)就是被剪裁過的。分區(qū)裁剪是一門將掃描限制在較少分區(qū)中的性能優(yōu)化技術(shù)。
--分區(qū)剪裁
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'
--不進(jìn)行分區(qū)剪裁
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'
執(zhí)行計劃
----------------------------------------------------------
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列作為分區(qū)列轰绵，但表使用year列作為分區(qū)鍵，執(zhí)行計劃中的第１步表明為證year=2000由于規(guī)則可能訪問其它分區(qū)而沒有推進(jìn)到視圖中尼荆，因為year是一個維度列左腔。因為分區(qū)鍵沒有前推進(jìn)視圖中，就不允許進(jìn)行分區(qū)剪裁耀找，從而需要掃描所有分區(qū)翔悠。
索引
選擇索引來提高使用model子句的sql語句性能與選擇索引提高其它的任何sql語句的性能都是一樣的业崖”┐眨可以使用訪問和篩選謂語確定最佳的索引策略叫挟。
--記住sql數(shù)據(jù)訪問索引
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'
執(zhí)行計劃
----------------------------------------------------------
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')
子查詢因子
在商務(wù)環(huán)境中，需求是很復(fù)雜的并且經(jīng)常需要多個層級的聚合汁汗。當(dāng)書寫復(fù)雜查詢時撮抓，將子查詢因子化與model子句結(jié)合起來妇斤，以避免sql語句變得復(fù)雜而不易管理。
--記住更多sql數(shù)據(jù)訪問索引的內(nèi)容
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;
小結(jié)
寫sql語句以集合的思維方式思考丹拯，很多sql語句都可以使用model子句來簡潔地進(jìn)行重寫站超。用model子句或分析函數(shù)進(jìn)行查詢重寫可以獲取比傳統(tǒng)的sql語句好得多的性能。子查詢因子化乖酬，model和分析函數(shù)特性的結(jié)合可以用來有效實現(xiàn)復(fù)雜的需求死相。
