Oracle^® Business Intelligence Applications Installation and Configuration Guide > Preinstallation and Predeployment Considerations for Oracle BI Applications > Miscellaneous Information About Oracle Business Intelligence Applications Deployments >

Performance Improvements for Change Capture for Incremental ETL Run

To maximize performance for Siebel (CRM) and Oracle E-Business Suite OLTP Systems, you need to implement the indexes specified in this section.

Implementing Transactional Database (OLTP) Indexes

To implement indexes, you use SQL files that are available in the \OracleBI\dwrep folder. The table below describes the SQL files that are appropriate for the specified applications.

The SQL files generate indexes on all S_.* tables that are being used by the out-of-the-box applications.

An Example Of Change Capture SQL And Required Indexes

Change capture SQL generates the following SQL:

Insert into S_ETL_I_IMG_XX (ROW_ID, LAST_UPD)

AS

SELECT ROW_ID, LAST_UPD, MODIFICATION_NUM

From

S_XXX

WHERE LAST_UPD > 'LAST REFRESH_DATE - PRUNE DAYS'

AND NOT EXISTS

(

SELECT 'X' FROM S_ETL_R_IMAGE

WHERE S_ETL_R_IMAGE.ROW_ID = S_XXX.ROW_ID AND

S_ETL_R_IMG_XX.MODIFICATION_NUM = S_XXX.MODIFICATION_NUM AND

S_ETL_R_IMG_XX.LAST_UPD = S_XXX.LAST_UPD

)

Based on the SQL above, the following indexes are created on the S_CONTACT table by the SQL generation scripts: