Databases Reference
In-Depth Information
Database parameters to use query rewrite
To use the query rewrite mechanism, two parameters have to be set in the initialization file or
server parameter file. Decide whether to enable it for all sessions, or to enable it for a specific
session only. In the latter case, use the
ALTER
SESSION
statement. The two parameters are:
F
QUERY_REWRITE_ENABLE=TRUE
F
QUERY_REWRITE_INTEGRITY
The value for the parameter
QUERY_REWRITE_INTEGRITY
can be set to one of the following
three values, depending on how the query is to be rewritten:
F
ENFORCED
: When this parameter is set to
ENFORCED
, the database guarantees
consistency and integrity of the data.
F
TRUSTED
: When this parameter is set to
TRUSTED
, the database can rewrite
queries using declared relationships that are not enforced by the database itself.
The optimizer trusts that the relationships are correct, so it uses declared but not
ENABLED
VALIDATED
primary or unique key constraints.
F
STALE_TOLERATED
: When the parameter is set to
STALE_TOLERATED
, the queries
can be rewritten even if the used relationships are not declared nor enforced by the
database, in the presence of data in the materialized views known to be inconsistent
with the data in the base table(s).
The last value for the parameter
QUERY_REWRITE_INTEGRITY
is often used when the
materialized views are not refreshed on commit, but on a recurring basis. This is done
during off-peak periods. The data is updated to the last refresh of the materialized view,
if we are not interested in up-to-date data.
The same parameters have to be enabled to use another functionality, function-based indexes.
The default values for these initialization parameters are
ENFORCED
for
QUERY_REWRITE_
INTEGRITY
and
TRUE
for
QUERY_REWRITE_ENABLE
.
Can I use materialized views in an OLTP environment?
Materialized views were definitely created to help data warehouse queries achieve better
performance, and for pre-calculating values to use later.
Surely, if we create a
REFRESH
ON
COMMIT
materialized view based on a table involved in
OLTP, we might experience very poor performance in our OLTP environment. This is because
data in the materialized view is updated for every transaction committed to the underlying
tables—the operation is executed during the commit phase—meaning longer execution time,
which is unacceptable for OLTP environments.
For example, materialized views can be used in such environments with the
REFRESH
ON
DEMAND
clause and execute the
FULL
refreshes during off-peak hours.
