This section provides a concise, abbreviated summary of the concepts and terminology required to discuss how ABL chooses the most efficient indexes to satisfy a query. For the purposes of index selection, there are three general types of WHERE clause.
Syntax
WHERE searchExpr[ BY field]
For example:
WHERE Cust-num > 6
WHERE Name "D" BY Sales-Rep
WHERE Country = "Zimbabwe"
WHERE Comments CONTAINS "Com*"
Syntax
WHERE searchExpr AND searchExpr[ BY field]
For example:
WHERE Cust-num > 6 AND comments CONTAINS "ASAP"
WHERE Name = "Rogers" AND Postal-Code BEGINS "017"
Syntax
WHERE searchExpr OR searchExpr[ BY field]
For example:
WHERE Cust-num > 1000 OR Sales-Rep BEGINS "S"
WHERE Postal-Code =< "01500" OR Postal-Code >= "25000"
The optional BYfield clause imposes a sort order on returned records and is called a sort match. A searchExpr typically has one of the following forms:
BY field
Sort match
field = expression
Equality match
field < / =< / > / >= expression
Range match
field BEGINS expression
Range match
wordIndexedfield CONTAINS stringExpression
Equality (simple string) Range (wild card string) None (>1 string, joined logically)
For more information, see the Record Phrase and FOR statement reference entries in OpenEdge Development: ABL Reference.
Because these expressions effectively select the records to return—and the indexes to use—they are called search conditions. Commonly, but not always, field is an indexed field. Also, a searchExpr can include other searchExpr's joined by ANDs and ORs, forming arbitrarily complex queries.
The Compiler constructs a logical tree from a query and evaluates both sides of each AND or OR, looking for index criteria. ABL counts equality, range, and sort matches (for OR) and uses them to select and bracket indexes. The precise rules are numerous and complex, and it is not important to fully understand their details. The next sections outline the rules in sufficient detail to help you develop a feel for index usage. In addition, you should experiment by coding various queries, compiling them with the XREF option, and examining index usage as reported in the SEARCH lines of the XREF output file.
The index selection examples that follow are based on the sports database.
