Oracle物化视图2 -- Query Rewrite及参数
来源:互联网 发布:java 异步日志 编辑:程序博客网 时间:2024/04/27 22:55
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 - 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_mviews 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, RELYFROM USER_CONSTRAINTSWHERE table_name='SALES';
可以看到外键关联都是not validated.当Query_Rewrite_Integrity=Enforced时,Oracle是不做Query Rewrite的
我们再查看下候选的MV的信息
select mview_name, build_mode,staleness from USER_MVIEWS;
候选的CAL_MONTH_SALES_MV是Prebuilt MV, 在Query_Rewrite_Integrity=Enforced时,Oracle是不会使用MV来重写该条SQL的。
一个简单的解决办法就是更改参数
Alter session set Query_Rewrite_Integrity=trusted;
NonValidated Rely Constraint与Query_Rewrite_Integrity=Enforced
对于普通物化视图(非Prebuilt),当Query_Rewrite_Integiry=Enforeced时,nonvalidatedrely外键依然支持Query Rewrite。 下面给出一个例子:
CREATE MATERIALIZED VIEW MONTHLY_SALES_MV BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITEAS 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的外键信息:
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, R_OWNER, R_CONSTRAINT_NAME, STATUS, VALIDATED, RELYFROM USER_CONSTRAINTSWHERE table_name='SALES';
show parameter rewrite;
NAME TYPE VALUE
-------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
我们来执行物化视图中的select语句,查看其执行计划:
可以看到Query Rewrite起作用了。
0 0
- Oracle物化视图2 -- Query Rewrite及参数
- ORACLE物化视图-Query Rewrite的一般理解之一
- ORACLE物化视图-Query Rewrite的一般理解之二
- ORACLE物化视图-Query Rewrite的一般理解之三
- ORACLE物化视图-Query Rewrite的一般理解之四
- ORACLE物化视图-Query Rewrite的一般理解之五
- 验证物化视图(materialized view)是否query rewrite
- Oracle物化视图2
- query rewrite的意思和dimension配合物化视图的巨大作用
- Oracle物化视图简介及实战
- Oracle物化视图简介及实战
- Oracle物化视图简介及实战
- oracle 物化视图(2)——物化视图语法!
- ORACLE物化视图-物化视图日志结构
- oracle的物化视图
- oracle的物化视图
- ORACLE中的物化视图
- Oracle的物化视图
- C# 为WebBrowser设置代理,打开网页
- Back of the Envelope Calculations
- iOS应用开发最佳实践:编写高质量的Objective-C代码
- 哎哟行行好啊让我收啊
- WSDL 详解
- Oracle物化视图2 -- Query Rewrite及参数
- JAVA集集合类
- Histograms of Oriented Gradients for Human Detection
- iOS开发:从新手到专家的一些建议
- 个人发展回顾年终总结
- Daily article(5)
- 【自由谈】城域网IPv6过渡技术——IVI
- [codility]equi
- ftpUtil实现文件上传和下载