Try OpenEdge Now
skip to main content
ABL Database Triggers and Indexes
Database Index Usage : How ABL chooses and brackets indexes to satisfy queries : General rules for choosing a single index
 

General rules for choosing a single index

When the selection criteria do not support multiple index usage, ABL uses these general rules (in this order) to select the most efficient index:
1. Use the index specified in a USE-INDEX option.
2. If there is a CONTAINS clause (which is legal only for word indexed fields), use the word index:
Sample WHERE clause
Indexes used
WHERE Customer.Comments CONTAINS "big"
AND Customer.Country = "Canada"
Comments
3. If an index is unique, and all of its components are used in active equality matches, use the unique index. It invariably returns 0 or 1 records:
Sample WHERE clause
Indexes used
WHERE Customer.Cust-Num = 10
AND Customer.Sales-Rep = "DR"
Cust-Num
4. Use the index with the most active equality matches. Equality matches are active if both of the following conditions are met:
*They apply to successive, leading index components.
*They are joined by ANDs (not ORs or NOTs).
This disqualifies equality matches on, for example, components 2 and 3 of an index with three components, and it disqualifies matches on components 1 and 2 if they surround an OR:
Sample WHERE clause
Indexes used
WHERE Customer.Country = "Costa Rica"
AND Customer.Postal-Code > "3001"
AND Customer.Sales-Rep BEGINS "S"
Country-Post
WHERE Customer.Name = "Harrison"
AND Customer.Sales-Rep BEGINS "S"
Name
WHERE Customer.Name = "Harrison"
AND (Customer.Country = "Finland"
OR Customer.Country = "Denmark")
Name
5. Use the index with the most active range matches. For a range match to be active it must stand alone or be connected to other selection criteria by ANDs. In addition, it must apply to an index component having any one of two properties:
*The component is the first or only one in the index.
*All preceding components in the index key have active equality matches.
The following table provides some index examples:
Sample WHERE clause
Indexes used
WHERE Customer.Sales-Rep = "ALH"
AND Customer.Country = "Italy"
AND Customer.Postal-Code BEGINS "2"
Country-Post
WHERE Customer.Contact = "DLC"
AND Customer.Sales-Rep BEGINS "S"
Sales-Rep
WHERE Customer.Contact = "Ritter"
AND Comments CONTAINS "compute*"
Comments
6. Use the index with the most sort matches (all sort matches are active):
Sample WHERE clause
Indexes used
WHERE Customer.Country BEGINS "EC"
AND Customer.Sales-Rep BEGINS "S"
BY Country
Country-Post
WHERE Customer.Contact = "Wilson"
AND Customer.Credit-Limit > 2000
BY Name
Name
WHERE Name = "Wilson"
OR Customer.Credit-Limit = 2000
BY Sales-Rep
Sales-Rep
7. If there is a tie—in other words, if multiple indexes have the same number of active equality, range, and/or sort matches—use the index that comes first alphabetically. If the PRIMARY index is one of the indexes in the tie, then use the PRIMARY index:
Sample WHERE clause
Indexes used
WHERE Customer.Name = "Samali"
AND Customer.Sales-Rep = "BCW"
Name
WHERE Customer.Country BEGINS "EC"
AND Customer.Sales-Rep BEGINS "B"
Postal-Code
8. Use the primary index:
Sample WHERE clause
Indexes used
WHERE Customer.Contact = "MK"
AND (Customer.Sales-Rep BEGINS "S"
OR Customer.Sales-Rep BEGINS "B")
Cust-Num
WHERE Customer.Postal-Code >= "01000"
AND Customer.City = "Boston"
Cust-Num
WHERE "meaningless expression"
Cust-Num