Database Reference
In-Depth Information
Especially for data marts, it isn't uncommon to use constraints that, although not validated by the database
engine, are known to be fulfilled by the data, thanks to the way the tables' data is (carefully) maintained. At the same
time, it's also not uncommon to have materialized views that, although considered stale by the database engine, are
known to be safe for rewriting queries.
To take advantage of general query rewrites in such situations, you can use the
query_rewrite_integrity
initialization parameter. With it, you can specify whether only enforced constraints (and therefore, validated by the
database engine) are to be used and whether a materialized view containing stale data is to be used. The parameter
can be set to the following three values:
•
enforced
: Only materialized views containing fresh data are considered for query rewrite.
Note that materialized views based on external tables are always considered stale. In addition,
only validated constraints are used for general query rewrite. This is the default value.
•
trusted
: Only materialized views containing fresh data are considered for query rewrite. In
addition, dimensions and constraints that aren't validated and marked with
rely
are trusted
for general query rewrite.
stale_tolerated
: All existing materialized views, including those with stale data, are
considered for query rewrite. In addition, dimensions and constraints that aren't validated
and marked with
rely
are trusted for general query rewrite.
The following example shows how to use general query rewrite without validating the constraint. As shown, the
constraint is marked with
rely
, and the integrity level is set to
trusted
:
•
SQL> ALTER TABLE sales MODIFY CONSTRAINT sales_customer_fk RELY;
SQL> ALTER SESSION SET query_rewrite_integrity = trusted;
SQL> SELECT upper(p.prod_category) AS prod_category,
2 sum(s.amount_sold) AS amount_sold
3 FROM sales s, products p
4 WHERE s.prod_id = p.prod_id
5 GROUP BY p.prod_category
6 ORDER BY p.prod_category;
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | MAT_VIEW REWRITE ACCESS FULL| SALES_MV |
--------------------------------------------------
If you're in trouble because one of your SQL statements isn't using query rewrite and you don't understand why,
you can use the
explain_rewrite
procedure in the
dbms_mview
package to find out what the problem is. The following
PL/SQL block is an example of how it's used. Notice that the
query
parameter specifies the query that should be
rewritten, the
mv
parameter specifies the materialized view that should be used for the rewrite, and the
statement_id
parameter specifies an arbitrary character string that's used to identify the information stored in the output table
rewrite_table
:
SQL> ALTER SESSION SET query_rewrite_integrity = enforced;
