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.