Slow Performance of Allocations Run Against View
Last updated on JULY 05, 2017
Applies to:Oracle Financial Services Profitability Management - Version 6.1 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
In Oracle Financial Services Profitability Management (PFT) 18.104.22.168, several Static Driver Allocations are taking over 10 hours each to run. The Allocations merge data into LEDGER_STAT from a custom view. The view creation statement includes a join on DIM_ORG_UNIT_B to populate the ORG_UNIT_ID column. The Allocations include a Hierarchy Filter on the ORG_UNIT_ID column.
The view has a processing key defined on ID_NUMBER and IDENTITY_CODE. The base table contains the required unique index on ID_NUMBER and IDENTITY_CODE.
The explain plan of the MERGE INTO statement shows a full table scan is done against the view. It is not using the available indexes.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms