oracle MView“启用查询重写”用法
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/4314435/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MView "enable query rewrite" usage
提问by Chandra Bhushan
CREATE TABLE TEST_DATE(COL1 VARCHAR2(20),COL2 NUMBER,COL3_DATE DATE,COL4_DATE DATE)
/
create materialized view TEST_SYS
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE --- ????
AS
SELECT COL1,COL2
FROM TEST_date
WHERE TRUNC(SYSDATE) BETWEEN TRUNC(COL3_DATE) AND TRUNC(COL4_DATE)
/
If Enable Query Rewrite option is disabled, MView is getting created for the above query then what is the purpose of having ENABLE QUERY REWRITE clause while creating materialized view, can we remove it and create it, and do we have to compromise on the performance of the MView if we have to comment Enable Query Rewrite.
如果禁用启用查询重写选项,则为上述查询创建 MView 那么在创建物化视图时使用 ENABLE QUERY REWRITE 子句的目的是什么,我们可以删除它并创建它吗,我们是否必须在性能上妥协? MView 如果我们必须注释启用查询重写。
Please explain me the use of enable query rewrite option in detail.
请详细解释启用查询重写选项的使用。
回答by Justin Cave
Query rewrite allows Oracle to rewrite a query against the base table (in this case TEST_DATE) to use the materialized view (in this case TEST_SYS) transparently. That is highly useful when your materialized view is pre-aggregating data, for example. If I have a transactions table and a materialized view
查询重写允许 Oracle 针对基表(在本例中为 TEST_DATE)重写查询以透明地使用物化视图(在本例中为 TEST_SYS)。例如,当您的物化视图预先聚合数据时,这非常有用。如果我有一个交易表和一个物化视图
CREATE MATERIALIZED VIEW mv_transaction_daily
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT store_id,
transaction_day,
SUM(transaction_amount) total_transaction_amount
FROM transactions
GROUP BY store_id, transaction_day
then Oracle could transform a query like
然后 Oracle 可以转换一个查询,如
SELECT store_id,
transaction_day,
SUM(transaction_amount) total_transaction_amount
FROM transactions
GROUP BY store_id, transaction_day
to use the materialized view rather than hitting the base table. And if you have appropriate dimension objects created, you could have a query like
使用物化视图而不是访问基表。如果你创建了适当的维度对象,你可以有一个查询
SELECT store_id,
trunc(transaction_day,'MM'),
SUM(transaction_amount) monthly_transaction_amount
FROM transactions
GROUP BY store_id, trunc(transaction_day,'MM')
that could also be rewritten to use the materialized view rather than the base table.
也可以重写为使用物化视图而不是基表。
If query rewrite is not enabled, you would only see a performance benefit from using the materialized view if you explicitly queried the materialized view rather than querying the base table. That generally requires more development effort and limits the ability of the DBAs to tune the application in the future by fine-tuning materialized views.
如果未启用查询重写,如果您显式查询物化视图而不是查询基表,您只会看到使用物化视图的性能优势。这通常需要更多的开发工作,并限制了 DBA 在未来通过微调物化视图来调整应用程序的能力。
In your case, the presence of SYSDATE in your WHERE clause is going to prevent query rewrite because Oracle wouldn't be able to figure out that a query against TEST_DATE would actually be able to use the materialized view. For all Oracle knows, data in the materialized view that satisfied the condition when the materialized view was refreshed no longer satisfies the condition and data that didn't make it into the materialized view now satisfies the condition simply because the SYSDATE has changed.
在您的情况下,您的 WHERE 子句中 SYSDATE 的存在将阻止查询重写,因为 Oracle 无法确定针对 TEST_DATE 的查询实际上能够使用物化视图。Oracle 都知道,刷新物化视图时满足条件的物化视图中的数据不再满足条件,而没有进入物化视图的数据现在满足条件只是因为 SYSDATE 已更改。
回答by erbsock
You do not need to have query rewrite enabled. Further, given your mview definition, it probably wouldn't hep you anyway.
您不需要启用查询重写。此外,鉴于您的 mview 定义,无论如何它可能不会帮助您。