Oracle DBMS_ADVANCED_REWRITE
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Contains interfaces for advanced query rewrite users. Using this package, you can create, drop, and maintain functional equivalence declarations for query rewrite whereby a
AUTHID CURRENT_USER
Character Set The character set must be set to WE8ISO8859P1 for this feature to work.
Dependencies
ALL_REWRITE_EQUIVALENCES DBMS_RWEQUIV_LIB DBMS_UTILITY
DBA_REWRITE_EQUIVALENCES DBMS_SYS_ERROR SUM$
Documented Yes
Exceptions
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
First Available 10.1
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.
Security Model Owned by SYS with no privileges granted

Recommend the following
GRANT execute ON dbms_advanced_rewrite TO uwclass;
GRANT create materialized view TO uwclass;
CREATE SYNONYM dbms_advanced_rewrite FOR sys.dbms_advanced_rewrite;
Source {ORACLE_HOME}/rdbms/admin/prvtxrmv.plb
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 through 12cR1 default is ENFORCED which is not compatible.
Subprograms
 
ALTER_REWRITE_EQUIVALENCE
Changes the mode of the rewrite equivalence declaration to the mode specified dbms_advanced_rewrite.alter_rewrite_equivalence(
name         IN VARCHAR2,
rewrite_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      CLOB,
destination_stmt CLOB,
validate         BOOLEAN  := TRUE,
mode             VARCHAR2 := 'TEXT_MATCH');
conn sys as sysdba@pdbdev

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@pdbdev

-- click here to download demo test data and save to c:\temp
-- create the demo tables and data by running servers.sql


SQL> @c:\temp\servers.sql

EXPLAIN PLAN FOR
SELECT srvr_id FROM uwclass.servers INTERSECT SELECT srvr_id FROM uwclass.serv_inst;

set linesize 121

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT srvr_id
FROM uwclass.servers s
WHERE EXISTS (
  SELECT srvr_id
  FROM uwclass.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 uwclass.servers INTERSECT SELECT srvr_id FROM uwclass.serv_inst', 'SELECT srvr_id FROM uwclass.servers s WHERE EXISTS (SELECT srvr_id FROM uwclass.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 uwclass.servers
INTERSECT
SELECT srvr_id FROM uwclass.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      VARCHAR2,
destination_stmt VARCHAR2,
validate         BOOLEAN  := TRUE,
mode             VARCHAR2 := 'TEXT_MATCH');
See The Overload 1 Demo Above
 
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');

Related Topics
Explain Plan
Outlines
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved