Using Materialized Views

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.

Conceptually similar indexed views and summary tables are present in Microsoft SQL platform and materialized query tables are available in Db2 z/OS platform.

For more information on materialized views, see the latest Oracle Database documentation.

To define a materialized view:

1. In PeopleSoft Application Designer, select File > New > 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.

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

   

6. Specify the Refresh Method. Options are:

   Field or Control	Description
   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	Description
   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	Description
   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:

• Understanding Record Definitions

• Running the Build Process

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 page (PTMAT_MAINT). Administrators must have the Materialized View Administrator role to have permission to access this page.

Access the Materialized View Maintenance run control page (PeopleTools > Utilities > Administration > Materialized Views > Maintain Materialized Views).

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

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

When you click the Run button, the PTMATREFVW Application Engine program executes. It brings up the Process Scheduler Request page. Enter the appropriate server on the page and click the OK button to initiate the process.

The following illustrates the PTMATREFVW Application Engine process that requires to be initiated for refreshing the materialized views.

Access the Change Properties page (PeopleTools > Utilities > Administration > Materialized Views > Enable Materialized Views).

You can enable or disable the materialized view feature on an application delivered view.

Change Properties page allows you to select views to enable or disable materialized views on them.

You can run DDDAUDIT queries to check for materialized views to resolve any inconsistency in the database:

• MVIEWS-1

• MVIEWS-2

• MVIEWS-3

See the section Materialized View Queries [Oracle] for detailed audit queries.

The materialized views are created from complex queries and aggregate data from various tables. Query Rewrite is a process that answers the queries using materialized views. It transforms a SQL statement that references tables and views into a statement accessing one or more materialized views that are defined on the detail tables.

For more information on Query Rewrite, see the latest Oracle Database documentation.

The following table lists the conditions for the various refresh mode/method combinations.

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.

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

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 ONDEMAND/FAST refreshes, and the data that needs to be refreshed may be small.