Configuring Siebel Business Applications > Improving the Performance of Siebel Business Applications > Using the Case Insensitivity Wizard to Improve Query Performance >

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 some 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 do 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 that your deployment uses an IBM DB2 database, if you query for the name Smith, then the object manager creates a query that is 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, it 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.