Except for looking up a record by ROWID (or RECID), OpenEdge queries ALWAYS use an index.
Proper bracketing and efficient use of indices are the keys to good query performance.
If a USE-INDEX phrase is used, that overrides/bypasses any other index selection.
In Progress V6 and prior, queries always use one index and one bracket.
In order for any given component of a multiple component index to be used, all preceding components of the index must be matched with equality matches and the expression components must be joined with AND operator.
If a set of criteria parses to <subexpr> OR <subexpr> [OR <subexpr>…]. Progress evaluates each sub expression individually looking for matches on leading components of indices. If each subexpression has matches ( either Range or Equality ) on leading index component, Progress will create brackets for each subexpression on the appropriate index. If any of the subexpressions fails to match an index, no brackets are used: a full table scan must be done and the normal single index selection rules will be used.
If a set of criteria parses to <subexpr> AND <subexpr> [AND <subexpr> …], each subexpression is again individually matched with indices. However, each subexpression must include equality matches on every component of the index in order to generate a bracket. Again, if any one of the subexpressions fails to match an index, only one bracket will be used, and the default rules for single indices will apply.
For single index selection, if there is a tie at any level between two or more indices the tie-breakers that are used:
- If one of the indexes is the primary index for the table, use that.
- Otherwise, use the index whose name comes first alphabetically.