Siebel Enterprise Integration Manager Administration Guide > EIM Performance Tuning > Troubleshooting EIM Performance >

Additional Indexes on EIM Tables

An examination of the data access path will assist you in determining whether additional indexes are necessary to improve the performance of the long-running SQL. In particular, look for table scans and large index range scans. For example, the following index was implemented to improve Step 10 of EIM. After evaluating the inner loop of the nested select, it was recommended to add an index on all T2 columns:

Inner loop:

(SELECT MIN(ROW_ID)

FROM siebel.EIM_ACCOUNT T2

WHERE (T2.T_ADDR_ORG__EXS = 'Y' AND

T2.T_ADDR_ORG__RID = T1.T_ADDR_ORG__RID AND

T2.IF_ROW_BATCH_NUM = 105 AND

T2.IF_ROW_STAT_NUM = 0 AND

T2.T_ADDR_ORG__STA = 0))

The index was created to consist of T2 columns used in the WHERE clause with ROW_ID at the end of the index. This influenced the database optimizer to choose this index for index-only access. Since the query wants the minimum (ROW_ID), the very first qualifying page in the index will also contain the lowest value.

NOTE:  Having the ROW_ID column as the leading index column would also be a good strategy. Since the ROW_ID is unique, the index is likely to be more selective.

Adding Indexes to Improve Performance of S_ORG_EXT

Table S_ORG_EXT has indexes on many columns, but not all columns. If you have a large number of records (several million accounts) in S_ORG_EXT, you may get a performance improvement in deleting and merging by adding an index to one or more of the following:

Before implementing any additional indexes, first discuss this with qualified support personnel.


 Siebel Enterprise Integration Manager Administration Guide 
 Published: 05 January 2004