Materialized views and “Query Rewrite” functionality
I have previously written a couple of articles on materialized views: one on general aspects of materialized views in SQL and PLSQL and another on refreshing materialized views in SQL and PL / SQL. In this article I am going to cover one of the functionalities supported by materialized views, a functionality known as QUERY REWRITE.
Query rewriting functionality:
It is clear that accessing a materialized view can be significantly faster than accessing all the base tables used when creating such a materialized view. It is for this reason that, if we have indicated this when creating the materialized view, the Oracle optimizer, if the query or query allows it, can rewrite the execution plan of said query to access the view instead of the base tables. Obviously, the rewriting of the query is transparent to the applications that are using it. So in some ways the use of the QUERY REWRITE is similar to the use of an index.
Users do not need to have special privileges on the materialized view to be able to use a query rewrite. Any query executed by a user who has permissions on the base tables involved can make use of the rewriting of the query by accessing the materialized view instead of accessing the base tables directly. This will only depend on what the optimizer decides.
On the other hand, the rewriting functionality of a query can be enabled or disabled when creating or modifying a materialized view:
CREATE MATERIALIZED VIEW view_name
...
[{ENABLE | DISABLE} QUERY REWRITE]
...
AS SELECT ... FROM ... WHERE ...
How to determine if the optimizer uses QUERY REWRITE:
The best way to find out if the optimizer is using the rewrite functionality of a query is to use the EXPLAIN PLAN command or the AUTOTRACE functionality. Another aspect to consider is that, if the optimizer uses a materialized view, then an improvement in the response time of the query should be observed.
QUERY REWRITE functionality control:
The rewrite functionality of a query can only be used when using the Oracle cost-based optimizer.
On the other hand, the dynamic parameter QUERY_REWRITE_ENABLED is used to configure a session or instance of an Oracle database, indicating whether or not we allow the optimizer to use the functionality of rewriting a query. If we set this parameter to FALSE, then we will have disabled the QUERY REWRITE functionality for that session or instance.
QUERY_REWRITE_INTEGRITY is another dynamic parameter that allows us to configure the rewriting functionality of a query for a session or instance. This parameter accepts the following values:
ENFORCED: This is the default value. If the parameter takes this value, then the optimizer will rewrite the queries only if it can guarantee the consistency of the delivered data. In other words, only materialized views that have been updated can be used by the functionality.
TRUSTED: The optimizer will rewrite the queries based on previously declared relationships without the need for them to have been imposed (ENFORCED). That is, all materialized views that are properly updated will be used by the query rewriting functionality.
STALE_TOLERATED: Whenever possible, the optimizer will use materialized views even if they are not updated. Obviously this can cause the query to return incorrect values.
There are also hints that allow the optimizer to influence the decision whether or not to use the QUERY REWRITE functionality when preparing the execution plan of a query, these hints are REWRITE and NOREWRITE.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals , Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.
Excellent article about query rewrite feature of Oracle. I have been working on data warehousing since 10+ years and I am using this feature. It is really very good feature.