Try OpenEdge Now
skip to main content
ABL Database Triggers and Indexes
Database Index Usage : How ABL chooses and brackets indexes to satisfy queries : Bracketing
 

Bracketing

Having selected one or more indexes to satisfy a query, the AVM tries immediately to isolate the smallest necessary index subset, so as to return as few records as possible. This is called bracketing. Careful query design can increase the opportunities for bracketing, thereby preventing ABL from scanning entire indexes and examining all records. The rules for bracketing are simple:
*Bracket on active equality matches.
*Bracket an active range match, but no further brackets are possible for that index.
The following table provides some bracketing examples:
Sample WHERE clause
Indexes used
Brackets
WHERE Contact = "DLC"
AND (Sales-Rep BEGINS "S"
OR Sales-Rep BEGINS "B")
Cust-Num
None
WHERE Postal-Code >= "01000"
AND City = "Boston"
Cust-Num
None
WHERE Name = "Harrison"
AND Sales-Rep BEGINS "S"
Name
Name
WHERE Contact = "DLC"
AND Sales-Rep BEGINS "S"
Sales-Rep
Sales-Rep
WHERE Country BEGINS "EC"
AND Sales-Rep BEGINS "S"
BY Country
Country-Post
Country-Post
WHERE Comments CONTAINS "big" AND Country = "USA"
AND Postal-Code = "01730"
Comments Country-Post
Country Postal-Code
The following recommendations are intended to help you maximize query performance. They are only recommendations, and you can choose to ignore one or more of them in specific circumstances:
*Avoid joining range matches with AND.
*Avoid ORs if any expression on either side of the OR does not use an index (or all its components). Be aware that the AVM must scan all records using the primary index.
*With word indexes, avoid using AND with two wild card strings, either in the same word index (WHERE comments CONTAINS “fast* & grow*”) or in separate word indexes (WHERE comments CONTAINS “fast*” AND report CONTAINS “ris*”).
*Avoid WHERE clauses that OR a word index reference and a non-indexed criterion (WHERE comments CONTAINS “computer” OR address2 = “Bedford”).