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.
影响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 ->
[*]
Enforced - This is the default mode. The optimizer only uses fresh data from the materialized views and only use those>
Query the user_mview to view the staleness of given materialized view.
[*] Trusted - In TRUSTED mode, the optimizer trusts that the>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>not ENABLED VALIDATED primary or unique key constraints and data>
也就是所，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,>
FROM USER_CONSTRAINTS
WHERE table_name='SALES';
可以看到外键关联都是not validated.
SELECT mview_name, build_mode,staleness,unknown_prebuilt
FROM user_mviews WHERE MVIEW_NAME='CAL_MONTH_SALES_MV'；
这是一个Prebuilt MV，在Query_Rewrite_Integrity=Enforced时，Oracle是不会使用MV来重写该条SQL的。
Alter session set Query_Rewrite_Integrity=trusted;
重新explain plan上一篇中的select语句，可以看到MV已经被使用了。
NonValidated> 对于普通物化视图（非Prebuilt），当Query_Rewrite_Integiry=Enforeced时，nonvalidated>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.
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, R_OWNER, R_CONSTRAINT_NAME, STATUS, VALIDATED,>
FROM USER_CONSTRAINTS
WHERE table_name='SALES';
show parameter rewrite;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
我们来执行一条物化视图中的select语句，查看其执行计划：
页:
[1]