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.
Conceptually similar indexed views and summary tables are present in Microsoft SQL platform and materialized query tables are available in DB2/zOS platform.
For more information on materialized views, see the latest Oracle Database documentation.
To define a materialized view:
In PeopleSoft Application Designer, select to create a new record.
Add fields to the record, and insert the SQL query using the Query Editor.
Select the Record Type tab.
Select SQL View or Query View for the Record Type, whichever is appropriate.
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
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.
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.
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.
Save the record.
Materialized Views are assigned automatically to the PSMATVW tablespace.
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:
Open the record in Application Designer.
Select the Record Type tab.
Select the Materialized View check box.
Specify the refresh method, refresh mode, and build option.
For detailed information about these options, see Defining Materialized Views.
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 ().
Image: Materialized View Maintenance page
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.
|
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 [Seconds] |
Displays the time interval in seconds for the refresh procedure to run. |
| Automatic Refresh |
Toggle the button to start a refresh schedule every time interval in seconds. |
| Set Refresh Interval [Seconds] |
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. |
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.
Image: PTMATREFVW Process on the Process List
The following illustrates the PTMATREFVW Application Engine process that requires to be initiated for refreshing the materialized views.
Access the Change Properties page ().
You can enable or disable the materialized view feature on an application delivered view.
Image: Change Properties page
Change Properties page allows you to select views to enable or disable materialized views on them.
|
Field or Control |
Definition |
|---|---|
| Enable? |
Select to convert the view to a materialized view. |
| Disable? |
Select to convert a materialized view to a normal SQL view. |
| Materialized View |
Search and select the view. You can use the prompt to select the name. |
| Status |
Displays enabled if the views are already selected to enable the materialized view feature. Displays disabled if the materialized views are already selected to disable the materialized view feature. |
| Object Type |
Displays the current status of the view as materialized or normal SQL view. |
| Stop Online Refresh |
Select to restrict the view from getting refreshed automatically. Note: Select to stop Pivot Grid initiated materialized views for automatic refresh. |
Note: Once you enable materialized views from PIA, go to the PeopleSoft Application Designer and build record to create the materialized view. Similarly, once you disabled the materialized views from PIA, go to the PeopleSoft Application Designer and build record to create the normal view.
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.
Note: PeopleSoft enables Query Rewrite only for those materialized views whose refresh method is FAST.
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.
|
|
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.
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.