CA Job Generated By PUM Never Creates Indexes On Materialized View HR_MSSCT_POSN_V
Last updated on JULY 31, 2017
Applies to:PeopleSoft Enterprise HCM Human Resources - Version 9.2 and later
Information in this document applies to any platform.
On : 9.2 version, HR Maintenance
CA job generated by PUM never creates indexes on views
Customer recently applied an update that modified HR_MSSCT_POSN_V, which is a materialized view. App designer defines an index on this view.
The CA job generated by the PUM never built the index, but when performing a manual Build on the project including the create index, an error was received due to duplicate keys.
The CA job has a step "Building the Create and Alter Scripts" which runs a build with everything but Create Views checked. If you do this on the project in app designer, even though create indexes is checked, it ignores the index on the view. This seems like reasonable behavior, since the view hasn't been built yet, and isn't part of the scope of this build.
Later the CA job has a step "Building the Create Views Script" which runs a build with just Create Views checked. This of course creates the view but not the index, since Create Indexes was not checked. If I go into app designer and do a build with Create Views and Create Indexes checked, it *does* add the statement to create the index to the generated SQL file.
If PeopleSoft is planning to deliver views with indexes on them, should the "Building the Create Views Script" be altered to have the Create Views option checked in the CA job? Or should another step or two be added to ensure these views are created? Or is it intentional that the indexes are not being created at the database level, despite being defined in app designer?
Should customers worry about the duplicate keys it's reporting in this case, or just leave the index off?
CA should create Indexes on all Views during the Create Views step, including Materialized Views
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms