Saturday, November 28, 2020
dbametrix
More
    Home Oracle DBA Tips Query Rewrite functionality

    Query Rewrite functionality

    The article explain in detail query rewrite functionality in materialized view with parameter control in Oracle database.

    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.

    - Advertisement -
    dbametrix

    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:

    - Advertisement -
    dbametrix

    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 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 be keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in 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 be enjoy more advance topics from our partner resource.

    Consider reading to these articles:

    - Advertisement -
    dbametrix

    1 COMMENT

    1. 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.

    Comments are closed.

    - Advertisment -
    dbametrix

    Most Popular

    Oracle Scheduler Jobs Example

    Blog post explains how to configure job scheduler in Oracle by example with how to manage, delete, drop, disable and enable jobs with how to check history of job if broken and enable.

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    Recent Comments