CUUG oracle物化檢視講解

1、檢視是什麼？
普通檢視：只是存放在資料字典當中的子查詢，本身沒有來源於基表

Oracle的物化檢視是包括一個查詢結果的資料庫對像，它是遠端資料的的本地副本，或者用來生成基於資料表求和的彙總表。物化檢視儲存基於遠端表的資料，也可以稱為快照。

 

  物化檢視可以用於預先計算並儲存表連線或聚集等耗時較多的操作的結果，這樣，在執行查詢時，就可以避免進行這些耗時的操作，而從快速的得到結果。物化檢視有很多方面和索引很相似：使用物化檢視的目的是為了提高查詢效能；物化檢視對應用透明，增加和刪除物化檢視不會影響應用程式中SQL語句的正確性和有效性；物化檢視需要佔用儲存空間；當基表發生變化時，物化檢視也應當重新整理。

 

  物化檢視可以查詢表，檢視和其它的物化檢視。

 

  通常情況下，物化檢視被稱為主表（在複製期間）或明細表（在資料倉儲中）。

  對於複製，物化檢視允許你在本地維護遠端資料的副本,這些副本是隻讀的。如果你想修改本地副本，必須用高階複製的功能。當你想從一個表或檢視中抽取資料時，你可以用從物化檢視中抽取。

  對於資料倉儲，建立的物化檢視通常情況下是聚合檢視，單一表聚合檢視和連線檢視。

在複製環境下，建立的物化檢視通常情況下主鍵，rowid,和子查詢檢視。

 

物化檢視由於是物理真實存在的，故可以建立索引。

2、物化檢視
   把查詢到的資料存放起來，為了以後再次查詢，有自己的資料
   好處：
   1.再次查詢就很方便
   2.省去查詢中所需要的資料的處理流程
  
   缺點：
   1.需要額外空間
  
   重新整理模式：
   完全重新整理：新truncate ，再執行查詢
   fast重新整理：根據物化檢視log的記錄，只重新整理改變的資料
  
  
3、建立一個能夠快速重新整理的物化檢視，要求物化檢視能夠消除表中的重複值

create table tm1(c1 int,c2 int， c3 int);
insert into tm1 values(1,2,3);
insert into tm1 values(2,2,3);
insert into tm1 values(4,2,9);
insert into tm1 values(1,2,3);
insert into tm1 values(1,6,7);

 

SQL> create materialized view log on tm1
  2  with rowid,sequence(C1,C2,C3)
  3  including new values;

實體化檢視日誌已建立。

如果是update 和delete 操作，需要在子查詢中新增count(*)c才支援立刻更新，預設是on demand

create materialized view MV_2
refresh fash on commit
as select c1,c2,count(*)
from tm1
group by c1,c2;

什麼是查詢重寫
查詢重寫就是把訪問基表的查詢修改寫成訪問物化檢視，從而提高查詢速度 特點：無需改變應用程式的程式碼，只需要建立一個允許查詢重寫的物化檢視

重寫的意思是：把訪問基表的查詢修改成訪問物化檢視
/*NOREWRITE*/

QUERY_REWRITE_ENABLED 初始化引數必須設定
成 TRUE.
? 查詢重寫許可權允許使用者在查詢的時候使用物化檢視.
? DBMS_OLAP 包允許物化檢視作為引數傳入.

查詢重寫例項
SQL> create MATERIALIZED VIEW sales_summary
2 tablespace data
3 parallel (degree 4)
4 BUILD IMMEDIATE REFRESH FAST
5 ENABLE QUERY REWRITE
6 AS
7 select p.prod_name, sum(s.quantity_sold),
8 sum(s.amount_sold)
9 from sales s, products p
10 where s.prod_id = p.prod_id
11 group by p.prod_name;

查詢重寫例項
SQL> select p.prod_name, sum(s.quantity_sold),
2 sum(s.amount_sold)
3 from sales s, products p
4 where s.prod_id = p.prod_id
5 group by p.prod_name;
SQL> select p.prod_name, sum(s.quantity_sold),
2 sum(s.amount_sold)
3 from sales s, products p
4 where s.prod_id = p.prod_id
5 group by p.prod_name;
OPERATION NAME
---------------------- -----------------
SELECT STATEMENT
TABLE ACCESS FULL SALES_SUMMARY

查詢重寫控制
? 初始化引數：
– OPTIMIZER_MODE
– QUERY_REWRITE_ENABLED
– QUERY_REWRITE_INTEGRITY
? 動態和會話級別引數:
– QUERY_REWRITE_ENABLED
– QUERY_REWRITE_INTEGRITY
? 新的暗示(hint): REWRITE 和NOREWRITE

 

禁用查詢重寫例項
SQL> select /*+ NOREWRITE */
2 p.prod_name, sum(s.quantity_sold),
3 sum(s.amount_sold)
4 from sales s, products p
5 where s.prod_id = p.prod_id
6 group by p.prod_name;
SQL> select /*+ NOREWRITE */
2 p.prod_name, sum(s.quantity_sold),
3 sum(s.amount_sold)
4 from sales s, products p
5 where s.prod_id = p.prod_id
6 group by p.prod_name;
OPERATION NAME
----------------------------- -----------
SELECT STATEMENT
SORT
HASH JOIN
TABLE ACCESS PRODUCTS
. . .
OPERATION NAME
----------------------------- -----------
SELECT STATEMENT
SORT
HASH JOIN
TABLE ACCESS PRODUCTS