Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

Progress Indexing and Bracketing Rules

« Go Back

Information

 
Article Number000021523
EnvironmentProduct: Progress
Version: 9.x
Product: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Question/Problem Description
Progress Indexing and Bracketing Rules.
Progress Indexing rules.
Progress Bracketing rules.
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
Rule 1: 
Except for looking up a record by ROWID (or RECID), OpenEdge queries ALWAYS use an index.

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 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.
Workaround
Notes
References to Other Documentation:

ABL Database Triggers and Indexes: Database Index Usage : How ABL chooses and brackets indexes to satisfy queries : General rules for choosing a single index
https://documentation.progress.com/output/OpenEdge117/openedge117/wp-abl-triggers/general-rules-for-choosing-a-single-index.html#

Progress Article(s):
000038175, Documentation: Index selection rules ambiguous and incomplete
Attachment 
Last Modified Date5/1/2017 11:06 AM