A secondary index is used to access data efficiently. Within TIBCO Object Service Broker you can define and build secondary indexes on selected fields of TDS table types.
Secondary indexes can improve the performance of data access by retrieving data based on secondary index values instead of just the primary key value. If you often search on a table for a value or set of values that is not a primary key value, you can improve the search time by defining a secondary index on the field or fields that you regularly use.
Secondary indexes incur administrative overhead. As described in the following sections, additional processes are required to define, build, and maintain secondary indexes.
You can define up to sixteen fields in a TDS table to be secondary index fields. To achieve optimum performance, the secondary key fields should have values that are evenly distributed with low duplication.
Secondary indexes are composed of a secondary key value, a primary key value, and a pointer to the data page where the corresponding occurrence resides. The KEY attribute in the table definition determines whether a field is a secondary key field. The following uppercase letters are valid secondary key values:
TIBCO Object Service Broker uses secondary indexes in retrieval when it is more efficient than reading the table data directly. An internal optimizer dynamically estimates each index access and chooses the index (or combination of indexes) that requires the least disk I/Os.
The selection criteria for retrieval can use ranges, and the equal to (=) and not equal (¬=) operators on a secondary index field for TDS tables. If multiple secondary index fields are specified with the equality operator, all are used.
If you want to copy the occurrences of a table to a target table that has secondary indexes defined, write a rule to perform the copy and commit the data often. The secondary indexes are being built on the target table during the copy, so more updates are being made than during a normal table copy. This requires you to commit your data more frequently.
Utilities for your operating environment for more information on the S6BBRSIX/hrnbrsix and S6BBRCLR/hrnbrclr utilities.