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 Database Data Warehousing Guide for more information about query rewriteThis chapter contains the following topics:
This section contains topics which relate to using the DBMS_ADVANCED_REWRITE
package.
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.
In addition, similar to the privilege required for regular materialized views, the user should be granted the privilege to create an equivalence. For example, the user er
can be granted this privilege by executing the following statement as SYSDBA
:
GRANT CREATE MATERIALIZED VIEW TO er;
This table list the all the package subprograms in alphabetical order.
Table 11-1 DBMS_ADVANCED_REWRITE Package Subprograms
Subprogram | Description |
---|---|
Changes the mode of the rewrite equivalence declaration to the mode you specify |
|
Enables the rewrite of top-level materialized views using submaterialized views. Oracle Corporation does not recommend you directly use this procedure |
|
Creates a declaration indicating that |
|
Drops the specified rewrite equivalence declaration |
|
Validates the specified rewrite equivalence declaration using the same validation method as described with the |
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);
Table 11-2 ALTER_REWRITE_EQUIVALENCE Procedure Parameters
Parameter | Description |
---|---|
|
A name for the equivalence declaration to alter. The name can be of the form |
|
The following modes are supported, in increasing order of power:
Oracle Corporation recommends you use the least powerful mode that is sufficient to solve your performance problem. |
This procedure enables the rewrite and refresh of top-level materialized views using submaterialized views. It is provided for the exclusive use by scripts generated by the DBMS_ADVISOR.TUNE_MVIEW
procedure. It is required to enable query rewrite and fast refresh when DBMS_ADVISOR.TUNE_MVIEW
decomposes a materialized view into a top-level materialized view and one or more submaterialized views.
Oracle does not recommend you directly use the BUILD_SAFE_REWRITE_EQUIVALENCE
procedure. You should use either the DBMS_ADVISOR.TUNE_MVIEW
or the DBMS_ADVANCED_REWRITE.CREATE_REWRITE_EQUIVALENCE
procedure as appropriate.
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');
Table 11-3 DECLARE_REWRITE_EQUIVALENCE Procedure Parameters
Parameter | Description |
---|---|
|
A name for the equivalence declaration. The name can be of the form Alternatively, a simple name that complies with the rules for a table name can be specified. In this case, the rewrite equivalence is created in the current schema. The invoker must have the appropriate |
|
A sub- |
|
A sub- |
|
A Boolean indicating whether to validate that the specified |
|
The following modes are supported, in increasing order of power:
Oracle recommends you use the least powerful mode that is sufficient to solve your performance problem. |
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);
Table 11-4 DROP_REWRITE_EQUIVALENCE Procedure Parameters
Parameter | Description |
---|---|
|
A name for the equivalence declaration to drop. The name can be of the form |
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);
Table 11-5 VALIDATE_REWRITE_EQUIVALENCE Procedure Parameters
Parameter | Description |
---|---|
|
A name for the equivalence declaration to validate. The name can be of the form owner.name, where owner complies with the rules for a schema name, and name compiles with the rules for a table name. Alternatively, a simple name that complies with the rules for a table name can be specified. In this case, the rewrite equivalence is validated in the current schema. The invoker must have sufficient privileges to execute both the |