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.
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.
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.
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.
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.