Enabling Oracle Function-based Indexes

Status

If you are...	This task is...
Installing a new iProcess Engine Version 11.8.0	Required
Upgrading from an iProcess Engine Version 11.0 or later	N/A

Database

Oracle

Description

The case_information table contains an Oracle function-based index, idx_ci_casedesc_UC, which allows iProcess Engine to perform case insensitive searches on the table.

Procedure

To enable the use of the idx_ci_casedesc_UC index in Oracle, you must:

1.

Set the following Oracle initialization parameters:

QUERY_REWRITE_INTEGRITY=TRUSTED; QUERY_REWRITE_ENABLED=TRUE;

2.

Ensure that the following SQL command is run at regular intervals (for example, as a scheduled job in the database or as part of your maintenance activities).

analyze table case_information compute statistics;

 

Note	On systems with large amounts of data, this command can take a long time to complete. TIBCO recommends that you run the command when there are not many users logged in, for example, overnight.

This command computes the necessary statistics on the case_information table, which the Oracle query optimizer uses to perform Cost-Based Optimization (CBO). If there is no cost-based information, or if CBO is disabled, searches involving the case_information table will require a full table scan, which can take a long time to complete.

See Also

See your Oracle documentation for more information about function-based indexes and CBO.