Pages

Subscribe:

Ads 468x60px

Showing posts with label QUERY_REWRITE_ENABLED. Show all posts
Showing posts with label QUERY_REWRITE_ENABLED. Show all posts

Sunday 13 May 2012

Query Rewrite in Oracle for Data Warehousing Application

Query Rewrite in Oracle for Data Warehousing Application



There are several ways that you can affect the optimizer’s logic in rewriting a query. First using the QUERY_REWRITE_ENABLED parameter, you can enable or disable the ability of the entire database to do query rewrites. This feature is very useful to maintain data warehouse database. Oracle supports so many good features and those are very useful in data warehousing database.


The QUERY_REWRITE_INTEGRITY parameter determines the freshness that is required of a materialized view. The options for this parameter are


ENFORCED:


The default setting for the QUERY_REWRITE_INTEGRITY parameter, this setting tells the optimizer to use only materialized views that contain fresh data. This setting also instructs the optimizer to use relationships that are based on enforced constraints like NOT NULL and Foreign key.


TRUSTED:

In this mode, the optimizer trusts that the data in the materialized views is correct and the relationships are correct. The optimizer also trusts declared but not enforced relationships and constraints, and it will use relationships specified by dimensions


STALE_TOLERATED:


In this mode, the optimizer trusts that the data in the materialized views is correct even if the views aren’t current and fresh. This mode offers the greatest flexibility in terms of rewrite capabilities but with the risk of returning incorrect results.


The safest level if rewrite integrity is ENFORCED. This setting guarantees that no data returned y the materialized view will be out of sync with a similar query against the base tables the base tables of the materialized view.


We can change above parameters in session level too using following commands.


Alter session set QUERY_REWRITE_ENABLED=true;
Alter session set QUERY_REWRITE_INTEGRITY=stale_tolerated;

This kind of questions can be asked during Oracle DBA interview questions and you can also need to take care for performance tuning during database services activities.