| QUERY_REWRITE_INTEGRITY [message #59179] |
Sun, 02 November 2003 23:40  |
Sujit Sarkar
Messages: 40
Registered: September 2003
|
Member |
|
|
Hi Friends,
Can any one explain what is the actual meaning
of QUERY_REWRITE_INTEGRITY=TRUSTED in simple term.
Thanx in Advance
Sujit
|
|
|
|
|
|
| Re: QUERY_REWRITE_INTEGRITY [message #59181 is a reply to message #59179] |
Mon, 03 November 2003 02:11  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Setting the value of the QUERY_REWRITE_INTEGRITY parameter determines how function-based indexes are used.
If the QUERY_REWRITE_INTEGRITY parameter is set to ENFORCED, then Oracle uses function-based indexes to for SQL expressions and SQL functions only and, if QUERY_REWRITE_INTEGRITY is set to any value other than ENFORCED, then Oracle uses the function-based index, even if it is based on a user-defined, rather than SQL function.
|
|
|
|
| Re: QUERY_REWRITE_INTEGRITY [message #59200 is a reply to message #59180] |
Tue, 04 November 2003 00:12 |
Sujit Sarkar
Messages: 40
Registered: September 2003
|
Member |
|
|
Thanx Maheer for nice Reply
I have still one doubt in case of TRUSTED value in QUERY_REWRITE_INTEGRITY you have written "Oracle allows rewrites using relationships that have been declared, but that are not enforced by Oracle"
What does this sentense mean. explain .
Waiting for you nice reply
Sujit
|
|
|
|
| Re: QUERY_REWRITE_INTEGRITY [message #59202 is a reply to message #59200] |
Tue, 04 November 2003 01:46 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
I don't have any experience with it myself, but here's what I found at a doc. I think it might clear things up a bit:
----------------------------------------------------------------------
In 9i data warehouse manual:
Accuracy of Query Rewrite
Query rewrite offers three levels of rewrite integrity that are controlled by the initialization parameter QUERY_REWRITE_INTEGRITY, which can
either be set in your parameter file or controlled using an ALTER SYSTEM or ALTER SESSION statement. The three values it can take are:
ENFORCED
This is the default mode. The optimizer will only use materialized views that it knows contain fresh data and only use those relationships that
are based on ENABLED VALIDATED primary/unique/foreign key constraints.
TRUSTED
In TRUSTED mode, the optimizer trusts that the data in the materialized views is fresh and the relationships declared in dimensions and RELY
constraints are correct. In this mode, the optimizer will also use prebuilt materialized views or materialized views based on views, and it will
use relationships that are not enforced as well as those that are enforced. In this mode, the optimizer also 'trusts' declared but not ENABLED
VALIDATED primary/unique key constraints and data relationships specified using dimensions.
STALE_TOLERATED
In STALE_TOLERATED mode, the optimizer uses materialized views that are valid but contain stale data as well as those that contain fresh
data. This mode offers the maximum rewrite capability but creates the risk of generating inaccurate results.
If rewrite integrity is set to the safest level, ENFORCED, the optimizer uses only enforced primary key constraints and referential integrity constraints to
ensure that the results of the query are the same as the results when accessing the detail tables directly. If the rewrite integrity is set to levels other
than ENFORCED, there are several situations where the output with rewrite can be different from that without it.
----------------------------------------------------------------------
HTH,
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
