Rule 1: OpenEdge queries ALWAYS use an index except when:
Rule 2: Proper bracketing and efficient use of indices are the keys to good query performance.
Rule 3:If a USE-INDEX phrase is used, that overrides/bypasses any other index selection.
Rule 4: In Progress V6 and prior, queries
always use one index and one bracket.
Rule 5: 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.
Rule 6: 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 (WHOLE-INDEX) must be done and the normal single index selection rules will be used.
Rule 7: 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.
Rule 8: 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.
