query_rewrite_integrity 的问题？

zero

oracle 当中关于查询重写的一致性设置参数一共有三个可选值：

  query_rewrite_integerity:
             values:
                       enforce
                       stale_tolerated
                       trusted

前面个两个都可以理解，trusted是如何解释的呢？什么条件下使用trusted？
最好能用例子说明一下？谢谢

Maclean Liu(刘相兵

FYI

enforced

Oracle enforces and guarantees consistency and integrity.

trusted

Oracle allows rewrites using relationships that have been declared, but that are not enforced by Oracle.

stale_tolerated

Oracle allows rewrites using unenforced relationships. Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data.



The setting of the parameter QUERY_INTEGRITY_LEVEL in the session where query
rewrite is attempted determines whether Constraints & Dimensions will be taken
into account.

A1) If QUERY_REWRITE_INTEGRITY = ENFORCED

- Dimensions are not used
- Primary Key, Unique, Foreign Key & NOT NULL constraints must be ENABLE
  VALIDATE to be used
- View constraints will not be used

A2) If QUERY_REWRITE_INTEGRITY = TRUSTED or STALE_TOLERATED

- Dimensions are used
- Primary Key, Unique and Foreign Key constraints must be ENABLE NOVALIDATE RELY
  or ENABLE VALIDATE to be used
- NOT NULL constraints can be in any state
- View constraints must be DISABLE NOVALIDATE RELY to be used

Maclean Liu(刘相兵

ODM FINDING:

TRUSTED Integrity Level
• Most practical setting for data warehouses
• MVs must contain fresh data
• Primary key, unique key, and foreign key
constraints should be ENABLED NOVALIDATE RELY
or ENABLE VALIDATE
• NOT NULL constraints can be in any state
• Uses relationships declared in dimensions
• Uses PREBUILT and view-based MVs
• Inaccurate results possible
query_rewrite_integrity = trusted


This setting is practical for most Data Warehouses because it is common practice for
constraints in such databases to be in ENABLED NOVALIDATE state. For such
constraints to be used for query rewrite, they must also be in RELY mode. Of course,
constraints will also be used if they are ENABLE VALIDATE (in this case
RELY/NORELY makes no difference).
MVs will be used for rewriting query blocks in more cases than the stricter
ENFORCED integrity level.
Inaccurate results may occur when:
• Relationships declared in dimensions are invalid (for example, when values at a
certain level of a dimension’s hierarchy do not roll up to exactly one parent
value). To avoid this problem, all dimensions should be validated using the
DBMS_OLAP.VALIDATE_DIMENSION procedure
• MVs are based on PREBUILT tables containing some data that violates
ENABLED NOVALIDATE constraints
• Relationships based on NOVALIDATE table or view constraints are bad because
some of the rows in the detail tables violate the constraints

Maclean Liu(刘相兵

STALE_TOLERATED Integrity Level
• Most flexible setting of all
• MVs can contain stale data
• Inaccurate results possible in more cases
query_rewrite_integrity = stale_tolerated

In some data warehouses, data in some MVs can be out of synchronization with data
in their detail tables because of differences in refresh schedules and pending
refreshes following bulk load or other DML operations. Such MVs will contain
STALE data. If it is acceptable to have some inaccuracy in the rewritten queries, the
STALE_TOLERATED integrity level can be used. In other respects, this integrity
level is similar to TRUSTED.

Maclean Liu(刘相兵

more useful information
http://docs.oracle.com/cd/B19306_01/server.102/b14223/qrbasic.htm

zero

thanks a lot. maclean. sorry, I am late.