什麼是物化檢視？

物化檢視是用於複製資料和提高查詢操作效能的資料庫物件。

引用Darl Kuhn的一句話來回答就是：“定期清空和載入查詢結果的表”

我們可以透過一個小例子來模擬最簡單的完全重新整理物化檢視的工作過程

實驗之前先明確兩個定義：

主表：SH.costs ，example中的示例表

基表：SH.mv_costs ，儲存查詢結果集的實體表，也就是“模擬物化檢視”的真正儲存資料的地方。

實驗一，模擬完全重新整理物化檢視

1. 主表為SH.costs

SH@pdb1 > select * from costs where rownum<10;

PROD_ID TIME_ID PROMO_ID CHANNEL_ID UNIT_COST UNIT_PRICE

---------- --------- ---------- ---------- ---------- ----------

14 17-JAN-98 999 3 890.98 1112.87

14 24-JAN-98 999 2 886.45 1108.99

14 03-MAR-98 999 2 904.18 1176.23

14 10-MAR-98 999 3 892.72 1159.99

14 14-MAR-98 999 4 863.64 1176.23

15 02-JAN-98 999 3 875.22 1013.99

15 17-JAN-98 999 4 846.71 999.99

15 19-JAN-98 999 4 846.71 999.99

15 02-FEB-98 999 4 863.64 1013.99

2. 假設主表的常用查詢如下

SH@pdb1 > select promo_id,sum(unit_cost) cost from costs group by promo_id;

PROMO_ID COST

---------- ----------

999 9168179.93

350 242949.08

351 258268

3. 使用CTAS方式建立一個表來儲存常用查詢的結果集，此刻它相當於物化檢視的基表。

SH@pdb1 > create table mv_costs as select promo_id,sum(unit_cost) cost from costs group by promo_id;

4. 物化檢視的基表執行完全重新整理的過程模擬如下。首先刪除基表中所有資料，然後再將源表的查詢結果集插入到基表中。

SH@pdb1 > delete mv_costs;

SH@pdb1 > commit;

SH@pdb1 > insert into mv_costs select promo_id,sum(unit_cost) cost from costs group by promo_id;

SH@pdb1 > commit;

5. 查詢物化檢視基表的資料。

SH@pdb1 > select * from mv_costs;

PROMO_ID COST

---------- ----------

999 9168179.93

350 242949.08

351 258268

實驗二，建立完全重新整理的物化檢視

模擬結束,接下來建立一個真正的物化檢視，跟上面模擬示例功能一樣的完全重新整理的物化檢視。

SH@pdb1 >

create materialized view mv_costs_1

segment creation immediate

refresh

complete

on demand

as select promo_id,sum(unit_cost) cost from costs group by promo_id;

透過下面的sql語句可以看到我們建立的物化檢視是由檢視、基表兩部分組成。基表是真正儲存查詢結果集的地方。

SH@pdb1 > col object_name for a20

SH@pdb1 > select object_name,object_type from user_objects where object_name like 'MV_COSTS_1' order by object_name;

OBJECT_NAME OBJECT_TYPE

-------------------- -----------------------

MV_COSTS_1 TABLE

MV_COSTS_1 MATERIALIZED VIEW

手工執行完全重新整理，該命令執行效果和模擬演示中delete mv_costs, insert into mv_costs select ...是一樣的。

SH@pdb1 > exec dbms_mview.refresh('MV_COSTS_1','C');

上述實驗對於剛剛接觸物化檢視的同學來說非常容易理解。我也從中收益過，特此分享一下。

12c 物化檢視 - 理解完全重新整理的物化檢視工作原理

相關文章