Kbase 21099: Multiple Index Selection Explained
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
21/06/2006 |
|
Status: Verified
GOAL:
How multiple index selection occurs in Progress, explained.
GOAL:
How multiple index selection occurs?
GOAL:
What is multiple index selection?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10
FIX:
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..