 |
|
Oracle query_rewrite_integrity
and query_rewrite_enabled
Oracle Tips by Burleson Consulting |
Question:
Oracle says you must have the
following initialization parameters defined to create a
function-based index:
QUERY_REWRITE_INTEGRITY =TRUSTED
QUERY_REWRITE_ENABLED = TRUE
But TRUSTED doesn't seems a good value, is there some problem if
I use enforced:
QUERY_REWRITE_INTEGRITY =ENFORCED
QUERY_REWRITE_ENABLED = TRUE
Which is the best option for using query_rewrite_integrity and
query_rewrite_enabled?
Answer:
First, it's important to note that
query_rewrite_integrity and query_rewrite_enabled are required
to use
materialized views and function-based indexes.
There are three acceptable values
for query_rewrite_integrity:
-
enforced
(default) - Presents materialized view with fresh data
-
trusted -
Assumes that the materialized view is current
-
stale_tolerated
- Presents materialized view with both stale and fresh
data
The problem here is
about the "freshness" of the data. One of the great
benefits if Materialized Views is that you don't have to choose
to constantly keep them synchronized with their base tables.
On many systems (especially Business Intelligence), having the
last days data is not relevant, and the materialized view can be
refreshed nightly via a schedule dbms_scheduler job. In
this case query_rewrite_integrity=stale_tolerated is
wonderful.
