Materialized Views

 

What are they

 

When building SQL views or query views on an Oracle database, you have the option of implementing a materialized view.

 

A traditional view has what is called a logical existence, but a materialized view has a physical existence.

A materialized view takes the results of SELECT statements and saves the result data to disk. This information is then available without the

need to run the SQL each time.

 

Using materialized views can provide significant improvements in performance, especially when complex SELECT statements are involved to gather the view data. 

 

 

Application Designer

 

Like other PeopleSoft record definitions, materialized views are defined using Application Designer. The data is refreshed on a schedule set

up in the materialized view record definition.  Those users with the Materialized View Administrator role have permission to access the

online page where maintenance of the view (and the refresh schedule) is setup.

 

MaterializedView Definition.bmp

Example of a materialized view definition in the Application Designer. 

 

A refresh schedule is set in the online page Materialized View Maintenance run control page.

 

PeopleSoft developers began utilizing materialized views as of PeopleTools 8.54.  

 

 

Positive aspects

 

Improved performance of queries that summarize job and payable time data, in PeopleSoft's Time and Labor Functionality to Support the

Affordable Care ActPeopleSoft's ACA processing, is one benefit.

Materialized views provide significant improvements in performance when used in Pivot Grids that use infrequently updated data.

 

 

Audit report sections

 

The audit report DDDAUDIT includes sections specific to materialized views on the Oracle platform: MVIEWS-1, MVIEWS-2, and MVIEWS-3.

These queries check for materialized views defined in the Application Designer but not in the database, or if defined in the database but not

in the Application Designer, or if a materialized view is missing a related language record.

 

As of PT 8.55, sections were added to DDDAUDIT for the

MicroSoft Sql Server Indexed View Queries:  IVIEWS-1 through IVIEWS-4.  Sections were also added to the audit report for the Db2 Zos

Materialized Query Tables:  MQT-1 through MQT-4.

 

DDDAUDIT solutions are posted to the PeopleSoft PeopleBooks / Data Management book.

 

 

Other platforms

 

Support for Materialized Views was first provided on the Oracle database platform in PeopleTools 8.54.

In PeopleTools 8.55, this was expanded to include materialized views on the MicroSoft SQL Server database platform, and materialized query

tables on the IBM DB2 z/OS database platform.

 

Additionally, PeopleTools 8.55 will provide the ability to create indexes on materialized views for Oracle, SQL Server, and DB2 z/OS.

 

To learn more

PeopleBooks > PeopleTools 8.55 > Administration Tools > Data Management > Administering Psft databases on Oracle > Using Materialized Views

 

PeopleSoft HCM - Time and Labor Functionality to Support the Affordable Care Act (Doc ID 1987962.1).  Attachments to this document are

specific to the Oracle and Db2 platforms.