Oracle物化檢視2 -- Query Rewrite及引數

Query Rewrite的條件

• Individual materialized views must have the ENABLE QUERY REWRITE clause.
• The session parameter QUERY_REWRITE_ENABLED must be set to TRUE (the default) or FORCE.
• Cost-based optimization must be used by setting the initialization parameter OPTIMIZER_MODE to ALL_ROWS, FIRST_ROWS, or FIRST_ROWS_n.
• Materialized View must be in 'Freesh' state.

物化檢視的Staleness

檢視User_MViews的Staleness列
FRESH - 無需說明。
STALE - 需要執行一次重新整理。
UNUSABLE - 需要執行一次完全重新整理，exec dbms_mview.refresh('XXX', 'C');
UNKNOWN - 表明這是個Prebuilt MV。也可以用Unknown_Prebuilt列，該列為'Y'，表明這是個Prebuilt MV。
UNDEFINED - 表示MV依賴的表在遠端資料庫上。
NEEDS_COMPILE - 表明物化檢視處在Invalid狀態。需要執行Alter materialized view xxx compile;來重新編譯物化檢視。也可以用Compile_state列。

影響Query Rewrite的引數 

• QUERY_REWRITE_ENABLED = TRUE (default), FALSE, or FORCE
•  QUERY_REWRITE_INTEGRITY - STALE_TOLERATED, TRUSTED, or ENFORCED (the default)
•  OPTIMIZER_MODE = ALL_ROWS (default), FIRST_ROWS, or FIRST_ROWS_n

詳細介紹Query_Rewrite_Integrity引數

Modifiable - ALTER SESSION, ALTER SYSTEM

 

• Enforced - This is the default mode. The optimizer only uses fresh data from the materialized views and only use those relationships that are based on ENABLED VALIDATED primary, unique, or foreign key constraints.

Query the user_mview to view the staleness of given materialized view.

• Trusted - In TRUSTED mode, the optimizer trusts that the relationships declared in dimensions and RELY constraints are correct. In this mode, the optimizer also uses prebuilt materialized views or materialized views based on views, and it uses relationships that are not enforced as well as those that are enforced. In this mode, the optimizer also trusts declared but not ENABLED VALIDATED primary or unique key constraints and data relationships specified using dimensions. This mode offers greater query rewrite capabilities but also creates the risk of incorrect results if any of the trusted relationships you have declared are incorrect.

也就是所，Oracle信任使用者宣告的資料完整性，MV中資料新舊程度，dimension中定義的關係。同時，當使用prebuilt materialized view時，需要使用這個設定。

• Stale_tolerated - In STALE_TOLERATED mode, the optimizer uses materialized views that are valid but contain stale data as well as those that contain fresh data. This mode offers the maximum rewrite capability but creates the risk of generating inaccurate results.

Trusted Query_Rewrite_Integrity例項

回到上一篇中遺留的問題。

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, R_OWNER, R_CONSTRAINT_NAME, STATUS, VALIDATED, RELY

FROM USER_CONSTRAINTS

WHERE table_name='SALES';

可以看到外來鍵關聯都是not validated.

select mview_name, build_mode,staleness  from USER_MVIEWS;

所有的Prebuilt MV的新舊程度(staleness)都是未知的。

在Query_Rewrite_Integrity=Enforced時，Oracle是不會使用MV來重寫該條SQL的。

Alter session set Query_Rewrite_Integrity=trusted;

重新explain plan上一篇中的select語句

NonValidated Rely Constraint與Query_Rewrite_Integrity=Enforced

對於普通物化檢視（非Prebuilt），當Query_Rewrite_Integiry=Enforeced時，nonvalidated rely外來鍵依然支援Query Rewrite。下面給出一個例子：
CREATE MATERIALIZED VIEW MONTHLY_SALES_MV 
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE
AS
  SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars , COUNT(s.quantity_sold) AS quantity_sold
  FROM sales s , times t
  WHERE s.time_id = t.time_id
  GROUP BY t.calendar_month_desc;

materialized view MONTHLY_SALES_MV created.

SALES_TIME_FK R SH TIMES_PK ENABLED NOT VALIDATED RELY
FROM USER_CONSTRAINTS
WHERE table_name='SALES';
 
show parameter rewrite;
NAME                                               TYPE        VALUE                                                                                                
-------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
query_rewrite_enabled                              string      TRUE                                                                                                 
query_rewrite_integrity                            string      enforced    

我們來執行一條物化檢視中的select語句，檢視其執行計劃：

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-1062827/，如需轉載，請註明出處，否則將追究法律責任。