MATERIALED VIEW --- ANALYZE MVIEWS

 ANALYZE MVIEWS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  

To analyzes the Materialized View to find out if it can use fast refresh, 

use the DBMS_MVIEW.EXPLAIN_MVIEW procedure which uses table MV_CAPABILITIES_TABLE.

1. Execute utlxmv.sql to create the MV_CAPABILITIES_TABLE .

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You must run the script in the same schema of the materialized view.

SQL> @$ORACLE_HOME/rdbms/admin/utlxmv.sql

2. Execute the DBMS_MVIEW.EXPLAIN_MVIEW procedure using the select statement of create Materialized View.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW ('<Use SELECT statement of the materialized view >');

Or if you have created the materialized view, use the materialized view name.

SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW ('< Materialized view name>');

3. Run the following query to find out if you can use the fast refresh and if not why cen be found in the MSGTXT column.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> set linesize 220

SQL> SELECT SUBSTR(capability_name,1,30)AS capability_name ,  possible, SUBSTR(related_text,1,10)AS related_text,

 SUBSTR(msgtxt,1,90)AS msgtxt FROM MV_CAPABILITIES_TABLE where capability_name like 'REFRESH%'ORDER BY seq;

Example:

~~~~~~~~~~

SQL> @ORACLE_HOME/rdbms/admin/utlxmv.sql

SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW ('SELECT * FROM <USER_NAME>.<Table_name>');

SQL> set linesize 220

SQL> SELECT SUBSTR(capability_name,1,30)AS capability_name ,  possible, SUBSTR(related_text,1,10)AS related_text,

 SUBSTR(msgtxt,1,90)AS msgtxt FROM MV_CAPABILITIES_TABLE where capability_name like 'REFRESH%'ORDER BY seq;

 

CAPABILITY_NAME                P RELATED_TEXT     MSGTXT

------------------------------ - --------------- ------------------------------------------------------------------------------------------

REFRESH_COMPLETE               Y

REFRESH_FAST                   N

REFRESH_FAST_AFTER_INSERT      N <USER_NAME>.<Table_name>       the detail table does not have a materialized view log

REFRESH_FAST_AFTER_ONETAB_DML  N                 see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML     N                 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT               N                 PCT is not possible on any of the detail tables in the materialized view

6 rows selected.