Return to Navigation

Using Materialized Views

This section provides an overview of materialized views and describes how to use materialized views with your PeopleSoft database on the Oracle platform.

When building SQL views or query views on an Oracle database, you have the option of implementing a materialized view. In contrast to a standard view, which has only a logical existence, a materialized view has a physical existence, and therefore it can be indexed, analyzed, and managed like other database tables. A materialized view takes the results of complex SELECT statements and saves the datasets to disk. The results are then readily available without the need to run the SQL each time.

Using materialized views can provide significant improvements in performance. The SELECT statements that typically define materialized views often contain sizable tables, complex joins, and summary functions that may take significant time and computing resources to complete. By running the SQL once and saving the results to a table that can be used and reused, a significant savings of CPU and memory consumption can be achieved. Like other PeopleSoft record definitions, materialized views are defined using Application Designer. The data is refreshed on a time period defined in the materialized view record definition. Materialized views are available only for Oracle databases.

To define a materialized view:

  1. In PeopleSoft Application Designer, select select File, then select New, then select Record to create a new record.

  2. Add fields to the record, and insert the SQL query using the Query Editor.

  3. Select the Record Type tab.

  4. Select SQL View or Query View for the Record Type, whichever is appropriate.

  5. Select the Materialized View check box.

    This check box is available only on the Oracle platform.

    The Materialized View Options appear.

    Image: Record Type tab for Materialized Views

    This example shows the Record Type tab when creating a materialized view on the Oracle platform

    Example of the Materialized Views options on the Record Type tab
  6. Specify the Refresh Method. Options are:

    Field or Control

    Definition

    Complete

    The materialized view table will be refreshed completely. Can be done at any time; time consuming.

    This is the default option.

    Fast

    Refreshes only modified rows. The materialized view table will be refreshed incrementally when running the Application Designer Build option.

  7. Specify the Refresh Mode. Options are:

    Field or Control

    Definition

    On Commit

    Refreshes occur whenever a commit is performed on one of the view's underlying detail table(s). Available only with single table aggregate or join-based views. This option has a performance impact since commit happens in the base table as well as the materialized view. During a commit, the Oracle system executes triggers and updates the materialized view log tables.

    On Demand

    Administrators refresh the view through the Materialized View Maintenance page. Can be used with all types of materialized views. This is the default option.

  8. Specify the Build Options. Values are:

    Field or Control

    Definition

    Immediate

    The system creates the view and populates the view from the base tables. This may be time consuming depending upon the complexity of the view and data.

    This is the default option.

    Deferred

    The system creates the view but does not populate the view during the build process. An administrator must run a refresh from the Materialized View Maintenance page.

  9. Save the record.

    Materialized Views are assigned automatically to the PSMATVW tablespace.

  10. Build the record, selecting Create Views in the Build dialog box.

For information about creating and building records, see:

To convert an existing view to a materialized view:

  1. Open the record in Application Designer.

  2. Select the Record Type tab.

  3. Select the Materialized View check box.

  4. Specify the refresh method, refresh mode, and build option.

    For detailed information about these options, see Defining Materialized Views.

  5. Save and build the record.

For ongoing maintenance of materialized views, administrators can use the Materialized View Maintenance run control page (PTMAT_MAINT).

Administrators must have the Materialized View Administrator role to have permission to access this page.

Image: Materialized View Maintenance Page

This image illustrates the fields and controls on the Materialized View Maintenance page.

Materialized View Maintenance page

This page contains a grid that is populated with a list of the records in the materialized views table, PSPTMATVWDEFN.

Field or Control

Definition

Owner

To limit the grid to records that belong to a specific owner, enter the owner name and click Search.

Refresh ?

Click to schedule a refresh for the record.

Materialized View Name

Lists the name of the record. You can change the value in this field.

Materialized

A display-only field that indicates if the record is materialized (Yes) or not (No).

Refresh Method

Lists the refresh method, either complete or fast.

Refresh Mode

Lists the refresh mode, either on demand or on commit.

Build Mode

Lists the build mode, either immediate or deferred.

Refreshed

Indicates if the record is refreshed (Yes) or not (No).

Refresh Interval

The time interval in seconds for the refresh procedure to run. Enter the interval in seconds to refresh the record every n seconds.

Last Refresh Date

List the date and time the record was last refreshed.

Staleness

Indicates if the record is stale or fresh based on the Oracle System Catalog.

Run

Click to execute the PTMATREFVW Application Engine program, to refresh the selected records.

You can run SYSAUDIT to check for materialized views with the following conditions:

  • MVIEWS-1

    This audit lists materialized views that are defined in Application Designer, but are not found in the database.

  • MVIEWS-2

    This audit lists materialized views that are defined in the database, but are not found in Application Designer.

  • MVIEWS-3

    This audit lists materialized views that are missing a related language record.

The following table provides guidelines for the various refresh mode/method combinations.

ON COMMIT/FAST REFRESH

ON DEMAND/FAST REFRESH

ON DEMAND/COMPLETE REFRESH

Use Case

Pivot Grid based on lookup and transaction tables

Pivot Grid based on lookup and transaction tables

Staging tables

Data Status (stale/fresh)

Always fresh

Relatively fresh

Stale data

Refresh Cost

Refresh is transparent and automatic. Happens at commit.

Refresh is fast

Refresh is time consuming.

Commit Cost

Commit will be slow depending upon the transaction rate on base tables.

Normal commit.

Normal commit.

DML Cost

Insert to base tables will be slower by a factor of two.

Insert to base tables will be slower by a factor of two.

Normal insertion.

Refer to the following recommendations for determining the appropriate refresh mode/method:

  • If requirements are for a materialized view with stale data, use an On Demand/Complete refresh.

  • If requirements are for a materialized view with fresh data and the base tables are lookup tables, then use an On Commit/Fast refresh.

  • If requirements are for a materialized view with fresh data and the base tables are not highly transactional, then use an On Commit/Fast refresh.

  • If requirements are for a materialized view with fresh data and the base tables are highly transactional, then use an On Demand/Fast refresh.

Image: Refresh Mode/Method Flowchart

This graphic provides a flowchart for determining the optimal refresh mode/method.

Flowchart for determining best refresh mode/method for materialized views

Refresh of materialized views on an Oracle Golden Gate or Oracle Active Data Guard environment is performed in the primary database, not the standby database. The time required to synchronize the primary database with the secondary database will depend upon the load and the environment. If any PeopleSoft applications are using materialized views on an Oracle Golden Gate or Oracle Active Data Guard environment, the data may be stale, depending upon the last refreshed date. Use the Materialized View Maintenance page to check on the status. Pivot grids are refreshed when they are initialized, as they incorporate materialized views that use On Demand/Fast refreshes, and the data that needs to be refreshed may be small.