Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved


Chapter 6 Secondary Indexes : Using Secondary Indexes

Using Secondary Indexes
What is a Secondary Index?
A secondary index is an index built from secondary key fields. Unlike a primary field, the values in a secondary index field do not have to be unique.
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.
Advantages
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.
Disadvantages
Secondary indexes incur administrative overhead. As described in the following sections, additional processes are required to define, build, and maintain secondary indexes.
How do you Create a Secondary Index?
You can create secondary indexes:
Although you can define fields to be secondary index fields in an empty table, the index is not actually created until data is present.
Criteria for Defining and Building a Secondary Index
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.
The following criteria also apply for building secondary indexes:
Secondary indexes cannot be built on a numeric field that contains null values.
What Comprises a Secondary Index?
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:
Data Access
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.
This optimization of data retrieval also occurs for the following data accesses:
Data accesses to subview (SUB) tables and calculation (CLC) tables, where appropriate, use the source table secondary index, if the source table has secondary indexes.
The tool COUNTOCCURRENCES uses secondary indexes if the selection is based on secondary index fields.
Selection Criteria
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.
Copying Definitions and Data
If you copy a table definition that has secondary indexes defined, the secondary indexes are not copied. You must define them separately.
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.
Clearing Data
If you want to clear a table with secondary indexes, use one of the following tools:
See Also
Utilities for your operating environment for more information on the S6BBRSIX/hrnbrsix and S6BBRCLR/hrnbrclr utilities.
TIBCO Object Service Broker Shareable Tools for more information about the COUNTOCCURRENCES and $CLRTAB tools.
TIBCO Object Service Broker Programming in Rules for more information about committing data.

Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved