Article

Multiple Index Selection Explained

« Go Back

Information

 
EnvironmentProgress 9.x
OpenEdge 10
Question/Problem Description
How multiple index selection occurs in Progress, explained.
How multiple index selection occurs?
What is multiple index selection?
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
Examples are given for, WHERE clauses using AND, WHERE clauses using OR, and WHERE clauses using CONTAINS.

The examples below use the sports2000.CUSTOMER table which has the following indexes:


Index Name   Index      Columns             Unique
----------- --------- ------------------  ----------
CustNum      (Primary)  CustNum             Yes
Name                    Name                No
SalesRep                SalesRep            No
CountryPost             Country,Postalcode  No
Comments                Comments Word Index
When the WHERE clause uses AND or OR clauses and indexes are available for both sides of the AND or OR, more than one index can be used.  The following rules and examples describe how the compiler uses multiple indexes:

It is important to note that multiple indexes are not used for queries using FIND.  FIND statements can only use one index.

-- WHERE using AND

When the selection criteria includes the use of AND, more than one index will be used when all the components of each index are involved in equality matches, and the indexes are not unique.

Example 1:


   FOR EACH customer WHERE name = "Peter" AND salesrep = "Jim":
Index Used: Name, SalesRep

Two indexes are used, because both indexes are non-unique and all components are involved in equality matches.

Example 2:


   FOR EACH customer WHERE name > "Martin" AND salesrep > "Jim":
Index Used: Name

Only one index is used, because the match is not an equality match.

Example 3:


   FOR EACH customer WHERE country = "USA" AND postalcode = "21000" AND salesrep = "Jim":
Index Used: CountryPost, SalesRep

Two indexes are used, because both indexes are non-unique and all components are involved in equality matches.

Example 4:


   FOR EACH customer WHERE country = "USA" AND salesrep = "Jim":
Index Used: SalesRep

Only one index is used, since not all the components of CountryPost are involved in the equality match.

Example 5:


   FOR EACH customer WHERE custnum = 65 AND salesrep = "Jim":
Index Used: Custnum

Only one index is used, because Custnum is unique.


-- WHERE using OR

When an OR is used in the WHERE clause and both the left and right side of the OR contain at least the lead component of an index using either the equality or range matches, then multiple indexes are used.

Example 1:


   FOR EACH customer WHERE (country = "USA" AND postalcode = "21000") OR (salesrep = "Jim"):
Index Used: CountryPost, SalesRep

Two indexes are used, since the selection criteria on both sides of OR use at least the leading components of both indexes.

Example 2:


   FOR EACH customer WHERE (country = "USA" AND postalcode = "21000") OR (salesrep > "Jim"):
Index Used: CountryPost, SalesRep

Two indexes are used, since the selection criteria on both sides of OR use at least the leading components o.f both indexes.

Example 3:


   FOR EACH customer WHERE (country = "USA") OR (salesrep = "Jim"):
Index Used: CountryPost, SalesRep

Two indexes are used, since the selection criteria on both sides of OR use at least the leading components of both indexes.

Example 4:


   FOR EACH customer WHERE (postalcode = "21000") OR (salesrep =
Jim"):
Index Used: Custnum

Since the postalcode is not the leading component, two indexes are not selected.  In addition, since an OR is used, Progress selects the primary index in this case.

Example 5:


   FOR EACH customer WHERE (name begins "J") OR (country = "USA"):
Index Used: Name, Countrypost

Two indexes are used, since both the matches are on the leading components of the index.  

Example 6:


   FOR EACH customer WHERE (custnum = 99) OR (custnum = 187):
Index Used: Custnum, Custnum

One index and two brackets used.

Example 7:


   FOR EACH customer WHERE (custnum < 99) OR (name = "John") OR (name = "Scott"):
Index Used: Custnum, Name, Name

Two indexes and three brackets used.


-- WHERE using CONTAINS

When a CONTAINS clause with a column that has a word index is used in the WHERE clause, the word index, as well as other indexes, will be used.

Example 1:


   FOR EACH customer WHERE (comments CONTAINS "amount") AND (salesrep = "Jim"):
Index Used: Comments, Salesrep

Comments is word indexed, therefore it is selected.  Also AND is used with the equality match, therefore salesrep is also selected.

Example 2:


   FOR EACH customer WHERE (comments CONTAINS "amount" AND name = "John") OR (country = "USA" AND postalcode = "21000"):
Index Used: Comments, Name, CountryPost

Comments index is word indexed, therefore it is selected.  The AND after the comments contains an equality match, therefore it's selected and the selection on the right of OR uses the lead components of the index, therefore it is selected..
Workaround
Notes
Attachment 
Feedback
 
Was this article helpful?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful.



Characters Remaining: 255

 



Copyright © 1993-2014. Progress Software Corporation. All Rights Reserved.
Previous MonthNext Month
SunMonTueWedThuFriSat