Working with Indexed Views

This section provides an overview of materialized views in PeopleSoft database on the Microsoft SQL platform. The indexed views and summary tables are materialized views that optimizes performance of a query.

Views are virtual tables whose contents are defined by a query. The query may reference tables and views from one or more databases.

Microsoft SQL server provides indexed views to optimize the performance of your database. When the queries need to aggregate lot of data from different tables and involve complex processing, then the performance of the system gets impacted.

If the view is referenced frequently and involves complex queries, create a unique clustered index on a view. When a unique clustered index is created on a view, it is stored in the database just like a table with a clustered index. Queries use the clustered index while retrieving data from a view. The efficiency of retrieving the data is improved.

Clustered indexes sort the data included in the view based on their key value. The key values are the columns included in the index definition. There can only be one clustered index per view, because the data rows themselves can be sorted in only one order.

Another benefit of creating an index on a view is that the optimizer starts using the view index in queries that do not directly name the view in the FROM clause. Existing queries can benefit from the improved efficiency of retrieving data from the indexed view without being re-coded.

For more information on indexed views see the Microsoft Developer Network website.

Summary tables are physical staging tables. The result of the SQL View is populated into the summary table. Accessing a summary table will return a table thereby improving the performance.

You can access the Materialized View Maintenance page if you are granted the Materialized View Administrator role. On this page you can select the indexed views and the summary tables for a refresh. A refresh happens when the data in tables are synchronized with the indexed views or summary tables. You can also get information on the staleness and last refresh date of the views.

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

The following example illustrates the Materialized View Maintenance page. The description of the fields and controls follows that:

Image: Materialized View Maintenance page

The page displays information on summary tables and indexed views. You can select a view to refresh its index.

Materialized View Maintenance page in Microsoft SQL server database.

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

Search and select a specific owner to limit the grid to records that belong to that owner.
Refresh?

Click to schedule a refresh for the record.
Indexed View/Summary Table Name

Enter the name of the indexed view or summary table. You can use the prompt for selecting the name.
Materialized

Displays if the indexed view or summary table is materialized in the database which makes them readily available in the database and the query does not have to be run again.
Indexed View?

Displays Yes, if the materialized view is an Indexed View.
Summary Table?

Displays Yes, if the materialized view is a Summary Table.
Refreshed

Indicates if the record is refreshed (Yes) or not (No).
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 Microsoft SQL System Catalog.
Run

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

On the page, select the indexed views/summary tables to refresh. Click the Save button. On saving the page, the Run button gets enabled.

Click the Run button. The Process Scheduler Request page with PTMATREFVW process is displayed. Click the OK button to initiate the process. The Application Engine program PTMATREFVW executes and recreates the indexes for each of the selected indexed views/summary tables.

For more information on Process Scheduler Request page see, Maintaining Materialized Views documented under the Oracle platform section.

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

You can see on the page, a list of the views delivered from applications. You can select specific views to enable or disable indexed views/summary tables feature. This example illustrates the Change Properties page. The columns on the page are explained below:

Image: Change Properties page

The page provides option to enable or disable indexed views and summary tables from the PeopleSoft Internet Architecture.

Change Properties page for materialized views in the Microsoft SQL Server.

Field or Control

Definition
Enable?

Select to convert the view as an indexed view or summary table.
Disable?

Select to convert the indexed view or summary table to a normal SQL view.
Indexed View/Summary Table Name

Enter the name of the indexed view or summary table. You can use the prompt for selecting the name.
Status

Displays Enabled or Disabled depending on the current status of the view.

Save the page after you select the views to enable or disable indexed views/summary tables. Open the ENABLEMV project in the Application Designer and build the project to render the selected views as indexed views/summary tables. Similarly, open the DISABLEMV project in the Application Designer and build it to convert the indexed views/summary tables to normal SQL views.

You can run the following DDDAudit queries for the indexed views to resolve any inconsistency in the database.

  • IVIEWS-1

  • IVIEWS-2

  • IVIEWS-3

  • IVIEWS-4

See the Indexed View Queries [Microsoft SQL Server] section for detailed audit queries.