| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/prvtxrmv.plb |
| First Available |
10.1 |
| Character Set |
The character set must be set to WE8ISO8859P1 for this feature to work. |
| Dependencies |
SUM$
|
all_rewrite_equivalences |
dbms_rwequiv_lib |
dbms_utility |
| dba_rewrite_equivalences |
dbms_sys_error |
user_rewrite_equivalences |
|
Error Messages |
| Error Code |
Reason |
| ORA-30388 |
Name of the rewrite equivalence is not specified |
| ORA-30391 |
The specified rewrite equivalence does not exist |
| ORA-30392 |
The checksum analysis for the
rewrite equivalence failed |
| ORA-30393 |
A query block in the statement did not rewrite |
| ORA-30396 |
Rewrite equivalence procedures require the COMPATIBLE
parameter to be set to 10.1 or greater |
|
Modes |
| Value |
Description |
| disabled |
Query rewrite does not use the equivalence declaration. Use this mode to temporarily disable use of the rewrite equivalence declaration. |
| general |
Query rewrite uses the equivalence declaration in all of its transformation modes against the incoming request queries. However, query rewrite makes no attempt to rewrite the specified destination_query. |
| recursive |
Query rewrite uses the equivalence declaration in all of its transformation modes against the incoming request queries. Moreover, query rewrite further attempts to rewrite the specified destination_query for further performance enhancements whenever it uses the equivalence declaration. |
| text_match |
Query rewrite uses the equivalence declaration only in its text match modes. This mode is useful for simple transformations. |
|
| Object Privileges |
execute on dbms_advanced_rewrite
create materialized view (or) create any materialized view |
GRANT execute ON
dbms_advanced_rewrite TO uwclass;
GRANT create materialized view TO uwclass;
CREATE SYNONYM
dbms_advanced_rewrite FOR sys.dbms_advanced_rewrite; |
Startup Parameters |
ALTER SYSTEM SET query_rewrite_integrity =
<'TRUSTED' | 'STALE_TOLERATED'>
SCOPE=<BOTH | MEMORY | SPFILE>; |
set linesize 121
col name format a30
col value format a30
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%rewrite%';
ALTER SYSTEM SET query_rewrite_integrity
= 'TRUSTED'
COMMENT='Permanent Change To System Configuration'
SCOPE=BOTH;
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%rewrite%';
-- the 10g and 11gR1 default is ENFORCED which is not compatible. |
| |
| ALTER_REWRITE_EQUIVALENCE |
| Changes the mode of the rewrite equivalence declaration to the mode
specified |
dbms_advanced_rewrite.alter_rewrite_equivalence(
name IN VARCHAR2,
mode IN VARCHAR2); |
|
exec dbms_advanced_rewrite.alter_rewrite_equivalence('UW',
'DISABLED'); |
| |
| BUILD_SAFE_REWRITE_EQUIVALENCE |
| Enables the rewrite of top-level materialized views using
sub-materialized
views |
dbms_advanced_rewrite.build_safe_rewrite_equivalence(
name IN VARCHAR2,
source_stmt IN CLOB,
destination_stmt IN CLOB,
check_sum IN BINARY_INTEGER); |
| TBD |
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. |
| |
| DECLARE_REWRITE_EQUIVALENCE |
Creates a declaration indicating that source_stmt is functionally equivalent to destination_stmt for as long as the equivalence declaration remains
enabled
Overload 1 |
dbms_advanced_rewrite.declare_rewrite_equivalence (
name VARCHAR2,
source_stmt VARCHAR2,
destination_stmt VARCHAR2,
validate BOOLEAN :=
TRUE,
mode
VARCHAR2 := 'TEXT_MATCH'); |
conn / as sysdba
GRANT execute on dbms_advanced_rewrite TO uwclass;
GRANT create materialized view TO uwclass;
CREATE OR REPLACE PUBLIC SYNONYM dbms_advanced_rewrite
FOR dbms_advanced_rewrite;
conn uwclass/uwclass
-- click
here to download demo test data and save to c: emp
-- create the demo tables and data by running servers.sql
SQL> @c: emp\servers.sql
EXPLAIN PLAN
FOR
SELECT srvr_id FROM servers INTERSECT
SELECT srvr_id FROM serv_inst;
set linesize 121
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
EXPLAIN PLAN
FOR
SELECT srvr_id
FROM servers s
WHERE EXISTS (
SELECT srvr_id
FROM serv_inst i
WHERE s.srvr_id = i.srvr_id);
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
BEGIN
dbms_advanced_rewrite.declare_rewrite_equivalence('UW',
'SELECT srvr_id FROM servers
INTERSECT
SELECT srvr_id FROM serv_inst', 'SELECT srvr_id FROM servers s WHERE EXISTS (SELECT srvr_id FROM serv_inst i WHERE s.srvr_id =
i.srvr_id)', TRUE, 'TEXT_MATCH');
END;
/
SELECT *
FROM user_rewrite_equivalences;
EXPLAIN PLAN
SET STATEMENT_ID = 'AFTER'
FOR
SELECT srvr_id FROM servers
INTERSECT
SELECT srvr_id FROM serv_inst;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
exec dbms_advanced_rewrite.drop_rewrite_equivalence ('UW');
EXPLAIN PLAN
SET STATEMENT_ID = 'AFTER'
FOR
SELECT srvr_id FROM servers
INTERSECT
SELECT srvr_id FROM serv_inst;
SELECT * FROM table(DBMS_XPLAN.DISPLAY); |
Overload 2 |
dbms_advanced_rewrite.declare_rewrite_equivalence (
name VARCHAR2,
source_stmt CLOB,
destination_stmt CLOB,
validate BOOLEAN :=
TRUE,
mode
VARCHAR2 := 'TEXT_MATCH'); |
DECLARE
sclob CLOB;
dclob CLOB;
BEGIN
sclob := CAST('SELECT srvr_id FROM servers
INTERSECT
SELECT srvr_id FROM serv_inst' AS CLOB);
dclob := CAST('SELECT srvr_id FROM servers s WHERE EXISTS (SELECT srvr_id FROM serv_inst i WHERE s.srvr_id =
i.srvr_id)' AS CLOB);
dbms_advanced_rewrite.declare_rewrite_equivalence('UW', sclob,
dclob, TRUE, 'TEXT_MATCH');
END;
/
exec dbms_advanced_rewrite.drop_rewrite_equivalence('UW'); |
| |
| DROP_REWRITE_EQUIVALENCE |
| Drops the specified rewrite equivalence declaration |
dbms_advanced_rewrite.drop_rewrite_equivalence(name IN VARCHAR2); |
| exec dbms_advanced_rewrite.drop_rewrite_equivalence('UW'); |
| |
| VALIDATE_REWRITE_EQUIVALENCE |
| Validates the rewrite equivalence declaration using the
specified mode |
dbms_advanced_rewrite.validate_rewrite_equivalence(name IN VARCHAR2); |
| exec dbms_advanced_rewrite.validate_rewrite_equivalence ('UW'); |
