Using indexes and sort sequence with selection, joins, or grouping
Before using an existing index, DB2 Universal Database™ for iSeries™ ensures the attributes of the columns (selection, join, or grouping columns) match the attributes of the key columns in the existing index. The sort sequence table is an additional attribute that must be compared.
The sort sequence table associated with the query (specified by the SRTSEQ and LANGID parameters) must match the sort sequence table with which the existing index was built. DB2 Universal Database for iSeries compares the sort sequence tables. If they do not match, the existing index cannot be used.
There is an exception to this, however. If the sort sequence table associated with the query is a unique-weight sequence table (including *HEX), DB2 Universal Database for iSeries acts as though no sort sequence table is specified for selection, join, or grouping columns that use the following operators and predicates:
- equal (=) operator
- not equal (^= or <>) operator
- LIKE predicate (OPNQRYF %WLDCRD and *CT)
- IN predicate (OPNQRYF %VALUES)
When these conditions are true, DB2 Universal Database for iSeries is free to use any existing index where the key columns match the columns and either:
- The index does not contain a sort sequence table or
- The index contains a unique-weight sort sequence table
- The table does not need to match the unique-weight sort sequence table associated with the query.
- Bitmap processing has a special consideration when multiple indexes are used for a table. If two or more indexes have a common key column between them that is also referenced in the query selection, then those indexes must either use the same sort sequence table or use no sort sequence table.
Parent topic:
Using indexes with sort sequence