PT 8.60
Randall Groncki

Introduction

Materialized Views are views that are pre-resolved by the database and stored for later use. They have a physical existence inside the database.

We can use Materialized Views to improve poor performance of complex SQL Views. That complex view is pre-rendered once and is then managed by the database. Queries using that view retrieve data from that pre-rendered table instead of re-executing the slow, complex SQL.

… but there is a catch.

Materialized views need refreshed as the data changes. Options range from complete scheduled refreshes to live updates based upon changes to the source data. You have to decide which is best for your application. The more immediate the refresh, the more complexity and maintenance required to support that materialized view.

Materialized views capability and performance also depend on your specific database platform.

Also, prepare for your DBAs and PeopleSoft Admins to generally grumpy and resistant to Materialized Views since this usually create extra work and headaches.

Used purposefully and sparingly, Materialized Views can improve the performance of some slow applications.

PeopleBooks Reference

Administrating PeopleSoft Databases in Oracle

The quality of PeopleBooks documentation on Materialized Views is mixed. Documentation for PeopleSoft Developers is informative and helpful. Unfortunately, the documentation for Admins is spare and totally inadequate.

Scope of this guide

This guide will walk through creating a Materialized View using the Complete Refresh method and setting up a schedule for those refreshes.

Create Materialized View

Record Definition

Create normal PeopleSoft record definition and specify it as a SQL View. Put in the SQL View text and build the view. Ensure the view works correctly in a SQL Tool such as SQL Developer.

In this example, X_JOB_CUR_MTV is a view of JOB that resolves for current EFFDT and EFFSEQ. There will be one entry per EMPLID / EMPL_RCD combination.

Copy the JOB Record Definition into this new record

Record Type is SQL View

The SQL to retrieve the current row for all employees.

Build the view and test the results

Make the Materialized View

Check the “Materialized View” checkbox on the Record Type Tab. A “Materialized View Options” dialog box opens to the right with the standard defaults.

Materialized View Options

Refresh Method

Complete	Refreshes the entire table.
Fast	Refreshes only modified rows

Refresh Mode

On Demand	Refreshes the view using the Materialized View Maintenance Page. This is the default option we will choose.
On Commit	Refreshes rows when the source data changes. Read PeopleBooks and test thoroughly before choosing

Build Options

Immediate	Create and populate the Materialized View when the view is built.
Deferred	The Materialized view is created, but not populated. Use the Materialized View Maintenance Page to populate the view with a batch process.

Build and test your view.

Refreshing your Materialized View

Your business requirements will determine how often the Materialized View needs refreshed. For a reporting table like the example above, a once per day refresh may be sufficient. Remember, each refresh requires time and resources. The users can access the stale version of the Materialized view until the database commits the refresh.

Navigate to the Materialized View Maintenance Run Control Page

PeopleTools > Utilities > Administration > Materialized Views > Maintain Materialized Views

Create a run control and enter the component.

Find your Materialized View on the list and select the “Refresh” Checkbox

After checking the Box, click on the “Save” button. After saving, the “Run” button will enable.

Click Run

Select the correct recurrence to refresh your view. You may have to create a new Recurrence if none of the existing recurrences fit your requirements.

This “Daily” recurrence will schedule the refresh process for 3:55AM Daily.

Submit the process.

More Refresh Options…

The “Materialized Views Maintenance” doesn’t quite work like a normal PeopleSoft Run Control Page. Saving your selections inserts records into the run control table behind the scenes. This is why you must Save after selecting at least one row before the Run button is enabled.

There are additional options for the database itself refresh the Materialized View based upon an interval of X seconds. Though this does offload the refresh to the database, it will then require a DBA to monitor and diagnose any issues with the refresh.

Work with your Admins to determine the best method to refresh your materialized views in your environment.

Summary

Materialized views can significantly improve the performance of some complex views. However, that performance gain must be balanced with the data freshness requirements and the higher maintenance costs of the view.