Understanding Materialized View Logs

During the source to OWS ETL load process, some jobs perform a cyclical redundancy check (CRC) to determine new or changed records. Unlike a traditional lookup process which targets the DTTM column for each record, the CRC process reads the entire record for every record in the source table and generates a CRC value to compare against the target warehouse record. Because the CRC process is so extensive it can create performance issues with the OWS jobs that use the logic.

To solve this problem, PeopleSoft provides Materialized View Log functionality for Campus Solutions, FMS, and SCM Warehouse customers using an Oracle database. The new feature will dramatically reduce the processing time for OWS jobs that use the CRC process.

Additionally, the new Materialized View Log functionality includes logic that identifies source record deletes so that those records can later be deleted from EPM fact tables.

In an Oracle database, a materialized view log is a table associated with the master table of a materialized view. When master table data undergoes DML changes (such as INSERT, UPDATE, or DELETE), the Oracle database stores rows describing those changes in the materialized view log. A materialized view log is similar to an AUDIT table and is the mechanism used to capture changes made to its related master table. Rows are automatically added to the Materialized View Log table when the master table changes. The Oracle database uses the materialized view log to refresh materialized views based on the master table. This process is called fast refresh and improves performance in the source database.

A materialized view log can capture the primary keys, row IDs, or object identifiers of rows that have been updated in the master table. The standard naming convention for a materialized view log table is: MLOG$_<master_name>.

INSERT /*+ append */ INTO PS_STDNT_BUDGET_IT SELECT * FROM
PS_STDNT_BUDGET_IT_TEMP WHERE EMPLID='FA0002';

Truncate table PS_STDNT_BUDGET_IT;