Oracle EBS: ICX_CAT_ITEMS_CTX_DTLS_TLP table資料量大的處理方法

問題: 在查看org item存檔速度問題時, 發現ICX_CAT_ITEMS_CTX_DTLS_TLP的資料有2G, 超過1700萬筆, 因此找了purge的方法(雖然事後證實這與現有存檔速度問題無關)

作法: 

1. Go into the responsibility: Purchasing.

2. Navigate to Reports / Run / Single Request

3. Execute Purge Catalog Intermedia Index (ICXPCIIP)

4. Create Index on ICX_CAT_ITEMS_CTX_DTLS_TLP 

a) Creating index

create index ICX.XX_ICX_CAT_ITEMS_CTX_TLP_99 on ICX.ICX_CAT_ITEMS_CTX_DTLS_TLP (INVENTORY_ITEM_ID,ORG_ID,LANGUAGE,SEQUENCE) online compute statistics parallel 8;

b) Disabling parallel on an index (example):

ALTER INDEX ICX.XX_ICX_CAT_ITEMS_CTX_TLP_99 NOPARALLEL;

c) Gathering stats on a table (example):

Exec fnd_stats.gather_table_stats('ICX','ICX_CAT_ITEMS_CTX_DTLS_TLP',100);

Ref:

1.Performance Issue with INVIDITM.fmb or INCOIN Involving ICX_CAT_ITEMS_CTX_DTLS_TLP (Doc ID 1925118.1)