Progress
Database Design
Guide


Case 2: WHERE searchExpr AND searchExpr

For a compound WHERE clause, Progress builds a logic tree and evaluates index usage on either side of the AND. When used with the FOR EACH statement, if both sides of the AND include equality matches on all components of non-unique indexes, both indexes are used. When used with the FIND statement, if both sides of the AND are equality matches on indexed fields, only a single index is used. Note that a word index expression with a simple string is an equality match; a wildcard string constitutes a range match:

Sample WHERE Clause
Indexes Used
WHERE Customer.Name = "Mary" 
 AND Customer.Sales-Rep = "Higgins" 
Name
Sales-Rep
WHERE Comments CONTAINS "small" 
  AND Country = "USA" 
  AND Postal-Code = "01730"  
Comments
Country-Post

If the selection criteria do not support multiple index usage, see the "General Rules for Choosing a Single Index" section:

NOTE: If Progress uses multiple indexes to select and return records, the precise return order is not predictable. If necessary, you can use the USE-INDEX or BY options to guarantee record return order. In the following example, the BY clause guarantees records are sorted by Cust-Num.

Sample WHERE Clause
Indexes Used
WHERE Customer.Country = "USA" 
 AND Customer.Sales-Rep = "Higgins"
 BY Cust-Num 
Sales-Rep


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095