How to manage Database Indexes in Sorting and Searching?
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 Server uses an index to efficiently retrieve and sort the result set of a query.
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.
The Sort Specification property for a business component, picklist, or predefined query orders the records retrieved in a query, and serves as the basis for the ORDER BY clause in the resulting 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, then you would need to create a custom index. Do not sort on joined columns, because indexes cannot be used.
The Search Specification property for 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 issued. An index needs to be present that supports the criterion. Otherwise, the RDBMS might 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.
Cubastion is among a very small number of specialized Siebel CRM Oracle Gold Partners in SME.