Siebel Tools Reference > Performance Improvement >

Database Indexes in Sorting and Searching

A database index is a data structure in the relational database system 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 database server 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 may need to add new indexes.

An index is added in Siebel Tools using the Index and Index Column object types. The index is added in the relational database as a result of its being created in Siebel Tools and database extensions being applied. The addition of custom indexes can adversely affect performance. You should discuss any custom index requirements with Siebel Expert Services.

The Sort Specification property of a Business Component object or picklist orders the records retrieved in a query, and serves as the basis for the ORDER BY clause in the SQL issued. An index needs to be present that supports the order specified in the sort specification. Otherwise, the RDBMS engine physically sorts the entire result set in a temporary table. The index needs to include the base columns for all of the fields, and to use them in the same order. There can be more columns specified in the index than are used in the sort specification, but the reverse is not true.

For example, the sort specification Last Name, First Name in the Contact business component is supported by at least one index on the S_CONTACT base table. One of these indexes is called S_CONTACT_U1, and it contains the LAST_NAME, FST_NAME, MID_NAME, PR_DEPT_OU_ID, OWNER_PER_ID and CONFLICT_ID columns, in that order. If you wanted a sort specification that ordered contacts in first-name order, you would need to create a custom index.

The Search Specification property in a business component, applet, link, or picklist selectively retrieves rows from the underlying table that meet the criterion specified in the property. The search specification is the basis for the WHERE clause in the resulting SQL. An index needs to be present that supports the criterion. Otherwise the RDBMS engine may scan through all rows in the table rather than only those to be returned by the query. The index needs to contain all the columns referenced by fields in the search specification.

NOTE:  In Sales Rep views such as My Accounts, if the user queries or sorts columns that are denormalized to the intersection table (for example NAME and LOC in S_ORG_EXT), performance is likely to be good. Siebel uses the intersection to determine visibility to records in the base table, and indexes can be used on the intersection table to improve performance.

If the query or sort includes columns not in the intersection table, performance is likely to degrade, because indexes are not used.


 Siebel Tools Reference, Version 7.5, Rev. A 
 Published: 18 April 2003