What is the Best Practice Approach to the Order of Columns in the FEM_BALANCES_P?
(Doc ID 414166.1)
Last updated on OCTOBER 04, 2016
Applies to:
Oracle Enterprise Performance Foundation - Version: 11.5.10Oracle Profitability Manager - Version: 11.5.10 to 12.0.0
Information in this document applies to any platform.
FEM
Goal
Need guidance on best approach to the order of columns on FEM_BALANCES_P, the primary, unique processing key index.
Built the index as:
drop INDEX FEM.FEM_BALANCES_P;
CREATE UNIQUE INDEX FEM.FEM_BALANCES_P
ON FEM.FEM_BALANCES
(CAL_PERIOD_ID
, LEDGER_ID
, DATASET_CODE
, COMPANY_COST_CENTER_ORG_ID
, LINE_ITEM_ID
, NATURAL_ACCOUNT_ID
, PRODUCT_ID
, INTERCOMPANY_ID
, ENTITY_ID
, CHANNEL_ID
, TASK_ID
, USER_DIM5_ID
, USER_DIM1_ID
, USER_DIM2_ID
, USER_DIM4_ID
, USER_DIM3_ID
, SOURCE_SYSTEM_CODE
, CURRENCY_CODE
, CURRENCY_TYPE_CODE
, FINANCIAL_ELEM_ID
, CREATED_BY_OBJECT_ID
)
PCTFREE 10 INITRANS 11 MAXTRANS 255
STORAGE(INITIAL 32768 FREELISTS 4 FREELIST GROUPS 4)
TABLESPACE APPS_TS_TX_IDX LOGGING ;
During operations of Oracle Profitability Manager (PFT) , found a series of statements that caused over 38 million disk reads. Examination of the columns showed that there is a lot of low selective columns at the start.
Q1: Can you change the ORDER of the Unique Index/Processing Key for FEM_BALANCES without invalidating existing Mapping Rules?
Q2: What is the best practice approach to the order of columns in the FEM_BALANCES_P index.?
Q3: During some operations we observed executions that caused huge I/O reads and high CPU?
Solution
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Goal
Solution
References