Kbase 21098: Single Index Selection Explained
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
08/02/2005 |
|
Status: Verified
GOAL:
How single index selection occurs in Progress, explained.
GOAL:
How single index selection occurs?
GOAL:
What is Single Index Selection?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
FIX:
The examples below use the sports2000.CUSTOMER table which has the following indexes:
Index Name Index Columns Unique
------------ --------- ------------------- ----------
Cust-Num (Primary) CustNum Yes
Name Name No
Sales-Rep Sales-Rep No
Country-Post Country,Postal-code No
Comments Comments Word Index
Single Index Selection:
When a query does not use ROWID or USE-INDEX, a single index or multiple indexes may be used to retrieve the requested data, based on the conditions specified in the WHERE or BY clause. The following rules are used by the compiler for single index retrievals:
1) Select an index which is unique and all the components are involved in the equality matches.
2) Select the index with more active equality matches.
3) Select the index with more active range matches.
4) Select the index with more active sort matches.
5) Select the index that is the primary index.
6) Select the first index alphabetically by index name.
Example 1:
FOR EACH customer:
Index Used: Custnum
Bracket on the whole index Custnum. The primary index Custnum is used since there is no WHERE clause.
Example 2:
FOR EACH customer WHERE state = "MD":
Index Used: Custnum
Bracket on the whole index Custnum. The primary index Custnum is used since there is no index on State. This will result in a full index scan to retrieve the rows.
Example 3:
FIND customer WHERE custnum = 12.
Index Used: Custnum
Single bracket on one index.
Example 4:
FOR EACH customer WHERE salesrep = "John":
Index Used: Salesrep
Single bracket on one index.
Example 5:
FOR EACH customer WHERE salesrep BEGINS "J"):
Index Used: Salesrep
Single bracket on one index.
Example 6:
FOR EACH customer WHERE custnum > 20 AND custnum < 40:
Index Used: Custnum
Single bracket on one index.
Example 7:
FOR EACH customer WHERE custnum > 56 BY name:
Index Used: Custnum
Single bracket on Custnum index. The index on name will not be used for sorting, instead internal sorting will be done once the rows are retrieved.
Example 8:
FOR EACH customer BY name:
Index Used: Name
Bracket on the whole index Name.
Example 9:
If a function or expression is used for the components of an index, an index or bracket will not be used.
FOR EACH customer WHERE SUBSTRING(name,1,1) = "A":
Index Used: Custnum
The index on Name will not be used, instead primary index on Custnum will be used. This expression will result in a full index scan to retrieve the rows.
Example 10:
FOR EACH customer WHERE (IF rowid-customer <> ? THEN ROWID(customer) = rowid-customer ELSE true):
Index Used: Custnum
In this case row will not be retrieved directly using the rowid. Because Progress selects the index at compile time, it will not be able to evaluate the IF statement. Therefore the primary index on Custnum will be selected, resulting in full index scan to retrieve the row.
Example 11:
FOR EACH custome.r WHERE name MATCHES "A*":
Index Used: Custnum
The index on Name will not be used, instead primary index on Custnum will be used. This expression will result in a full index scan to retrieve the rows..