How To Use DBMS_MVIEW.EXPLAIN_REWRITE and EXPLAIN_MVIEW To Diagnose Query Rewrite and Fast Refresh Problems
(Doc ID 149815.1)
Last updated on DECEMBER 18, 2020
Oracle Database - Enterprise Edition - Version 18.104.22.168 to 22.214.171.124 [Release 9.0.1 to 11.2] Oracle Database - Personal Edition - Version 126.96.36.199 to 188.8.131.52 [Release 9.0.1 to 11.2] Oracle Database - Standard Edition - Version 184.108.40.206 to 220.127.116.11 [Release 9.0.1 to 11.2] Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Information in this document applies to any platform.
This document shows how to use the procedures EXPLAIN_REWRITE and EXPLAIN_MVIEW in the DBMS_MVIEW package to gather useful information when troubleshooting problems with query rewrite using materialized views (MV), and MV fast refresh.
The EXPLAIN_REWRITE procedure can help understand why a query does not get rewritten to use a Summary Materialized View.
EXPLAIN_MVIEW shows the query rewrite capabilities available to a particular Summary Materialized View, and also shows if a materialized view is fast-refreshable or not.
This document applies to all RDBMS versions >= 9i.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!