Grouping, joining, and selection: Open Query File (OPNQRYF) command performance

 

When using an existing index, the optimizer ensures that the attributes of the selection, join, and grouping fields match the attributes of the keys in the existing index.

Also, the sort sequence table associated with the query must match the sequence table (a sort sequence table or an alternate collating sequence table) associated with the key field of the existing index. If the sequence tables do not match, the existing index cannot be used. However, if the sort sequence table associated with the query is a unique-weight sequence table (including *HEX), some additional optimization is possible. The optimizer acts as though no sort sequence table is specified for any grouping fields or any selection or join predicates that use the following operators or functions:

• *EQ

• *NE

• *CT

• %WLDCRD

• %VALUES

The advantage is that the optimizer is free to use any existing access path where the keys match the field and the access path either:

• Does not contain a sequence table.

• Contains a unique-weight sequence table (the table does not have to match the unique-weight sort sequence table associated with the query).

 

Parent topic:

Open Query File (OPNQRYF) command: Performance considerations for sort sequence tables