Oracle 参数 QUERY_REWRITE_INTEGRITY 官方解释，作用，如何配置最优化建议

本站中文解释

：

QUERY_REWRITE_INTEGRITY参数用于设置Oracle的查询Rewrite功能的完整性。它有三个可用的值：

enum query_rewrite_integrity

{REWRITE_HIGH = 0, //查询重写保持完整性，这是默认设置
REWRITE_LOW, //查询重写可能会影响结果集的一致性
REWRITE_MEDIUM //查询重写允许但不能保证结果集的一致性
};

低级设置可以执行更高效的查询，但是可能会导致查询结果的一致性问题。而中级设置允许Oracle查询重写，但结果不能保证一致性。高级设置则保证查询重写不会影响结果一致性。

要正确设置QUERY_REWRITE_INTEGRITY，首先根据自身数据库的情况来确定该参数的设置级别。如果你的数据库中有大量的临时变量和存储过程，可能会导致查询重写改变结果，则应当谨慎高级设置。但是，如果你的数据库中的表的数据内容不变且存储过程和临时变量也不变，则可以使用低级/中级设置，从而改善查询性能。

要设置QUERY_REWRITE_INTEGRITY参数，可以使用以下语句：

alter system set query_rewrite_integrity={REWRITE_HIGH | REWRITE_LOW| REWRITE_MEDIUM};

特别要注意的是，这个参数只在重启数据库之后才会生效，也就是说必须重新启动数据库才能使参数设置生效。

官方英文解释

QUERY_REWRITE_INTEGRITY determines the degree to which Oracle must enforce query rewriting. At the safest level, Oracle does not use query rewrite transformations that rely on unenforced relationships.

QUERY_REWRITE_INTEGRITY is relevant for materialized views as well as for foreign key constraints in NOVALIDATE state.

Values

• 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. You must set the QUERY_REWRITE_INTEGRITY initialization parameter to stale_tolerated before querying an external table in the In-Memory Column Store (IM column store).

If a foreign key constraint is in NOVALIDATE state, join elimination is not done when QUERY_REWRITE_INTEGRITY=enforced. This means that queries with joins over a foreign key constraint that is in RELY NOVALIDATE state can potentially take longer to parse and execute as the optimizer does not trust the RELY.

See Also:

• “INMEMORY_CLAUSE_DEFAULT”

• Oracle Database Data
  Warehousing Guide for more information about query rewrite for materialized views

• Oracle Database Data
  Warehousing Guide for more information about the QUERY_REWRITE_INTEGRITY parameter