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.
