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:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.