Managing Database Indexes in Sorting and Searching

This topic is part of Guidelines for Data Objects Layer.

A database index is a data structure in the RDBMS that is associated with a table. It provides references to all records in the table for quick lookup and filtering, and is sorted in a particular order for sorting in that order quickly. The Siebel database uses an index to efficiently retrieve and sort the result set of a query.

Indexes provided in the Siebel Data Model are tuned for optimal performance of standard Siebel applications. When you add new business components with custom sorting or filtering requirements, you need to make sure that a database index is present that supports the requirement and delivers the result set efficiently. You might need to add new indexes.

You add indexes using the Index and Index Column object types. The index is added in the database as a result of its being created in Siebel Tools and database extensions being applied.

Note: The addition of custom indexes does not always improve performance and can reduce performance in some cases. The incremental value of an index depends in large part on the heterogeneity and distribution of the data.

When data is heterogeneous, all or most of the values are unique (such as with row ID values, which are unique). The less heterogeneous the data (that is, the more homogeneity or repeated instances of values), the less benefit the index offers relative to its costs.

For Boolean fields, indexes generally offer little value. Some performance benefit might be found when querying for the least commonly represented values. Little or no benefit is found when querying on more commonly represented values or values that are evenly distributed. Similar guidelines apply for other homogeneous data, such as fields that are constrained to a list of values.

Indexing generally improves performance of SELECT operations. However, it can significantly reduce performance for batch UPDATE and INSERT operations, such as are performed by Siebel EIM.

Discuss any custom index requirements with Oracle Advanced Customer Services. Contact your Oracle sales representative to request assistance from Oracle Advanced Customer Services.