Progress
Database Design
Guide


Case 3: WHERE searchExpr OR searchExpr

For a compound WHERE clause, Progress builds a logic tree and evaluates index usage on either side of the OR. In general, if all selection criteria on both sides of the OR include matches—equality, range, or sort—on successive, leading components of two non-unique indexes, Progress uses both indexes:

Sample WHERE Clause
Indexes Used
WHERE Customer.Comments CONTAINS "to*" 
   OR Customer.Name = "Carlin" 
Comments
Name
WHERE Name > "Beaudette"
   OR Country > "Zambia" 
Name
Country-Post

In addition, if one side of the OR includes a CONTAINS clause (that is, it uses a word index), Progress uses the word index and then a second index to satisfy the other side of the OR:

WHERE Comments CONTAINS "credit"
   OR Postal-Code > "01000" 
Comments
Cust-Num

In this example, the right side of the OR includes a range match, but Postal-Code is the second component of the County-Post index, so the match is not active. Progress uses the primary index to satisfy this piece of the query and, as always, uses the word index to satisfy a CONTAINS clause as shown in this example:

WHERE Comments CONTAINS "credit"
   OR Postal-Code < "01000" 
   BY Sales-Rep 
Comments
Sales-Rep

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

NOTE: If any expression on either side of the OR does not use an index or all its’ components, Progress must scan all records using the primary index.


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