Configuring Siebel Business Applications > Improving the Performance of Siebel Business Applications >
How a CIAI Index Can Improve a Query
The CIAI query is a feature that uses an index to support a case-insensitive and accent-insensitive (CIAI) query on certain text columns. The purpose of the CIAI query is to improve query performance. If a database uses a CIAI index to perform a search, then the Siebel database is not required to perform table scans to locate records, and the database can perform the search more quickly.
For example, in the S_CONTACT table, assume the LAST_NAME column is defined for a CIAI query and uses the LAST_NAME_CI column. Assuming an IBM DB2 database, if you query for the name Smith, then the object manager creates a query similar to the following:
SELECT column list FROM S_CONTACT
WHERE LAST_NAME_CI = SMITH
The Siebel database then uses the CIAI index on LAST_NAME_CI to locate the records.
For Text and CLOB physical types, the Case Insensitivity Wizard does the following work:
- Accepts the Text or CLOB physical type
- Does not create a CIAI column or CIAI indexes for a Text or CLOB physical type
- Sets the Default Insensitivity property to DB Case & Accent
For more information, see Types of Tables and Columns That CIAI Query Supports.
How Siebel CRM Implements a CIAI Column and Index in a Database
The type of database determines how Siebel CRM implements a CIAI column or index that exists in the Siebel repository. For example, Siebel CRM implements a CIAI column in the following ways:
- On MS SQL, as a calculated column
- On Oracle, as indexes that use functions
- On IBM DB2, as a schema column
Effect of CIAI Columns on Sorts
If querying on a column that is configured for CIAI, then the ORDER_BY clause might or might not include the CIAI column depending on the following situations:
- A view with the Visibility Applet Type property set to All uses the CIAI column in the ORDER_BY clause.
- A view with the Visibility Applet Type property set to Org does not use the CIAI column for sorts.
If querying on another column in the same view that is not configured for CIAI, then Siebel CRM does not use the CIAI column in the ORDER_BY clause.