Working with Indexed Views
This section provides an overview of materialized views in PeopleSoft database on the Microsoft SQL Server database platform. The indexed views and summary tables are materialized views that optimize 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.
Note: There are different editions of Microsoft SQL Server. Indexed views and summary tables for Microsoft SQL Server are supported only for Microsoft SQL Server Enterprise Editions.
Microsoft SQL Server provides indexed views to optimize the performance of your database. When the queries need to aggregate a 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. Select . Select a run control or add a new one.
The page displays information on summary tables and indexed views. You can select a view to refresh its index. The grid on this page is populated with a list of the records in the materialized views table, PSPTMATVWDEFN.
This example illustrates the Materialized View Maintenance page, followed by the description of the fields and controls on the page.
Field or Control |
Description |
|---|---|
Owner |
Search and select a specific owner to limit the grid to records that belong to that owner. |
|
Select All |
Select this option to choose all of the rows. |
Refresh? |
Select this option 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 run 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 ().
The page includes 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 for Microsoft SQL Server database, followed by descriptions of the columns on the page.
Field or Control |
Description |
|---|---|
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.