1. Configuring Siebel Business Applications
  2. Guidelines for Creating a Function-Based Index

Guidelines for Creating a Function-Based Index

As of Siebel CRM 22.8 Update, developers can work with their database administrators to create function-based indexes in Siebel Tools where they are deemed appropriate for performance reasons in your deployment. Examples where function-based indexes can improve query performance include:

  • Operations on multiple columns

  • Case insensitivity

  • Partial content searches (for example, the last four digits of a U.S. Social Security Number)

Note: Function-based indexes are currently supported for Oracle Database and IBM DB2 databases only.

Function-based indexes are created in the Siebel repository. The user interface for creating a function-based index for a table is the same as that for creating a standard index in Siebel Tools. Once a function-based index has been created, all of the Siebel CRM utilities and other functions that manage the database schema will respect these indexes, such as PostInstallDBSetup, Apply DDL, Generate DDL, the Siebel Migration application and related REST APIs, DDLImp, DDLExp, and other command-line utilities.

Caution: It is not supported to manually create your own function-based indexes outside of the Siebel repository or using methods other than those described here. All changes to the Siebel CRM database schema must be made through the Siebel repository.
Note: Like other indexes that you create for Siebel CRM, function-based indexes are intended to be created only as result of a specific performance issue for some common query that users encounter. Your Siebel CRM development team creates function-based indexes only with the participation of your database administrator.

After the initial creation of a function-based index, this index would not need to be recreated to stay up to date, because when each database record is subsequently created or updated, the query function (expression) is calculated and the result is stored in the index at that time.

More specifically, when a query performance issue is identified, the developer contacts the database administrator to analyze existing indexes on a given table and determine whether it might be appropriate to address the performance issue using a function-based index. Once the database administrator has defined the desired solution, the developer defines the index in the Siebel repository based on the information provided by the database administrator. The developer then applies the data definition language (DDL) directly or generates the DDL and provides the DDL file to the database administrator to apply.

In defining a function-based index, the developer adds one or more index column objects. In addition to those index columns containing expressions (functions), other index columns can be added that specify physical table columns that might be part of the same query. Note the following:

  • For index columns in support of a function-based index, the Expression Flag property must be checked and a value must be specified for the Expression property.

  • For index columns based on physical columns, leave Expression Flag unchecked and do not specify an expression.

In an index column containing an expression, a valid Column Name value must be specified (due to existing unique key constraints on the table), but the actual column is ignored when the index is generated. Where Expression Flag is checked and the Expression property is set to the database expression or function you require for this index, when the index is generated, this expression is used instead of the column specified in Column Name. See also Siebel Object Types Reference.

Also consider the following information:

  • The information provided by the database administrator representing the solution to the query problem might need to be divided into separate parts, to be mapped to different index column objects when the index is defined (where, for example, some index column records might include expressions and some physical columns).

  • Each expression in a function-based index must not exceed 255 characters.

  • Function-based index support is added through installation of Siebel CRM 22.8 Update or later. When PostInstallDBSetup runs, it makes the changes to the Siebel repository necessary to store the Expression and Expression Flag data in the repository table S_INDEX_COLUMN, which stores all index columns. No further customer action is required to enable this feature (such as running the RepositoryUpgrade utility.)

Example of a Function-Based Index

The following is a hypothetical example scenario of a customer determining a need and creating a function-based index to improve the query performance. Assume a base table CONSULTANT_USAGE that is used to store usage information about consultants working on projects. For each consultant in the table working on a particular project, the Days column stores the number of days the consultant is expected to spend on the project. The Rate column stores the daily rate for this consultant. The table stores 1,000 such consultant records for this large project.

In this example, project managers frequently query this table to identify high-value consultants for whom the number of project days multiplied by the consultant's rate is greater than $10,000. Without a function-based index created to query for this information, the database engine must load all 1,000 records in the table and the expression of Days multiplied by Rate must be calculated before the engine can filter out the records that do not qualify, as follows:

SELECT * FROM CONSULTANT_USAGE WHERE DAYS*RATE > 10000 WHERE PROJECT_ID = 'x'

As the size of this table increases, the performance of this query might worsen and it might be deemed a candidate for an index. Upon examination, the database administrator would note that a calculation is being performed in the where clause and might conclude that this is the cause of the performance problem and recommend a function-based index including the expression DAYS*RATE. If such a function-based index is created, then the query can be created to directly load only those records where DAYS*RATE > 10000, which is a much more efficient and better-performing query.