Skip to main content

Analyzing Materialized Views for Fast Refresh

The article will describe how materialized views can be analyzed and optimized to ensure they can be FAST REFRESHED, through this way refresh time will reduced from more than 5hours to less than 1hour. 

The tools used to perform the FAST REFRESH analysis are:
–         DBMS_MVIEW
–         MV_CAPABILITIES_TABLE
The Oracle provided DBMS_MVIEW.explain_mview procedure is used to analyze each of the existing materialized views FAST REFRESH capabilities and write the results to the MV_CAPABILITIES_TABLE.
The MV_CAPABILITIES TABLE is created in the owner’s schema by running the following Oracle 9i script:

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

Steps:
–        Created the necessary materialized view logs for each of the master tables making sure to include all columns referenced in any of the materialized view aggregations along with the ROWID, SEQUENCE, and INCLUDING NEW VALUES clauses based on the Oracle 9i documentation FAST REFRESH requirements.

–        Created materialized views including the REFRESH FORCE ON DEMAND option, in order to prepare for the detailed analysis of the respective FAST REFRESH

Comments

Post a Comment

Popular posts from this blog

How to Use ALTERNATE archive destination

How to Use ALTERNATE archive destination We can define an archive destination with value ‘ALTERNATE’, which will take over if primary destination is full. As per Oracle documentation “An archiving destination can have a maximum of one alternate destination specified. An alternate destination is used when the transmission of an online redo log from the primary site to the standby site fails”. In fact this is the concept of data guard, but can also be applied on standalone system. SQL> show parameter recovery NAME TYPE VALUE ------------------------- ----------- ------ db_recovery_file_dest string +fradg SQL> show parameter LOG_ARCHIVE_DEST_1 NAME TYPE VALUE ------------------- ----------- ------------------------------ log_archive_dest_1 string location=use_db_recovery_file_dest SQL> show parameter log_archive_dest_state_1 NAME TYPE VALUE -------------------------- ----------- ------- log_archive_dest_state_1 string enable SQL> alter system set...
Post a Comment
Read more

RMAN Different errors and their Solution

RMAN Different errors and their Solution Backup Fails with Invalid RECID Error: RMAN-20035, RMAN-6038 When you attempt a backup and receive the following error messages: RMAN-3014: Implicit resync of recovery catalog failed RMAN-6038: Recovery catalog package detected an error RMAN-20035: Invalid high RECID error It indicates the control file and the recovery catalog is not synchronized. RMAN detects that the control file currently in use is older than the control file previously used to resynchronize. Cause: This due to any of the scenario you are restore a backup controlfile through a non-oracle mechanism and then open the database through Resetlogs option or making a copy of control file through operating system utility and trying the restore on new system through RNAN. You do not use catalog so RMAN does not get any information regarding this process.  The recovery catalog indicates that the highest RECID is 100, but the control file indicates that the highe...
Post a Comment
Read more

Changing National Character Set AL16UTF16 to UTF8

Changing National Character Set AL16UTF16 to UTF8 The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB.  In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB. Like the database character set, the national character set is defined when the database is initially created and can usually no longer be changed, at least not easily or without involving quite a lot of work (export, recreate database, import). Except when creating the database, where the national character set is defined explicitly, it can change implicitly even when upgrading the database from Oracle8i to Oracle9i (or Oracle10g). You require SYSDBA authorization to change the national character set. Changing the national character set means changing an Oracle Dictionary entry, but no data is changed.  $sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> Select property_...
Post a Comment
Read more