PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
DBMS_ADVANCED_REWRITE
contains interfaces for advanced query rewrite users. Using this package, you can create, drop, and maintain functional equivalence declarations for query rewrite.
See Also:
Oracle Data Warehousing Guide for more information about query rewrite |
This chapter contains the following topics:
No privileges to access these procedures are granted to anyone by default. To gain access to these procedures, you must connect as SYSDBA
and explicitly grant execute access to the desired database administrators.
You can control security on this package by granting the EXECUTE
privilege to selected database administrators or roles. For example, the user er
can be given access to use this package by the following statement, executed as SYSDBA
:
GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO er;
You may want to write a separate cover package on top of this package for restricting the alert names used. Instead of granting the EXECUTE
privilege on the DBMS_ADVANCED_REWRITE
package directly, you can then grant it to the cover package.
This procedure changes the mode of the rewrite equivalence declaration to the mode you specify.
DBMS_ADVANCED_REWRITE.ALTER_REWRITE_EQUIVALENCE ( name VARCHAR2, mode VARCHAR2);
This procedure creates a declaration indicating that source_stmt
is functionally equivalent to destination_stmt
for as long as the equivalence declaration remains enabled, and that destination_stmt
is more favorable in terms of performance. The scope of the declaration is system wide. The query rewrite engine uses such declarations to perform rewrite transformations in QUERY_REWRITE_INTEGRITY=trusted
and stale_tolerated
modes.
DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ( name VARCHAR2, source_stmt VARCHAR2, destination_stmt VARCHAR2, validate BOOLEAN := TRUE, mode VARCHAR2 := 'TEXT_MATCH'); DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ( name VARCHAR2, source_stmt CLOB, destination_stmt CLOB, validate BOOLEAN := TRUE, mode VARCHAR2 := 'TEXT_MATCH');
Query rewrite using equivalence declarations occurs simultaneously and in concert with query rewrite using materialized views. The same query rewrite engine is used for both. The query rewrite engine uses the same rewrite rules to rewrite queries using both equivalence declarations and materialized views. Because the rewrite equivalence represents a specific rewrite crafted by a sophisticated user, the query rewrite engine gives priority to rewrite equivalences over materialized views when it is possible to perform a rewrite with either a materialized view or a rewrite equivalence. For this same reason, the cost-based optimizer (specifically, cost-based rewrite) will not choose an unrewritten query plan over a query plan that is rewritten to use a rewrite equivalence even if the cost of the un-rewritten plan appears more favorable. Query rewrite matches properties of the incoming request query against the equivalence declaration's source_stmt
or the materialized view's defining statement, respectively, and derives an equivalent relational expression in terms of the equivalence declaration's destination_stmt
or the materialized view's container table, respectively.
This procedure drops the specified rewrite equivalence declaration.
DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE ( name VARCHAR2);
This procedure validates the specified rewrite equivalence declaration using the same validation method as described with the VALIDATE
parameter in "VALIDATE_REWRITE_EQUIVALENCE Procedure".
DBMS_ADVANCED_REWRITE.VALIDATE_REWRITE_EQUIVALENCE ( name VARCHAR2);