Kbase P127007: In a WHERE clause OF does not always determine index selection
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
20/11/2007 |
|
Status: Unverified
GOAL:
Does the OF option always determine index selection?
GOAL:
How does the OF option determine index selection?
GOAL:
What is the effect of OF option on index selection?
GOAL:
Are the ABL OF and the SQL ON the same?
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Versions
FIX:
The Progress 4GL/ABL allows the use of the OF option in a query predicate for a FIND, FOR EACH or OPEN QUERY statement to imply fields to match when resolving the query.
For instance, EACH invoice OF customer would simply be converted to 'WHERE invoice.custnum EQ customer.custnum'.
In SQL you have the ON option which allows you to specify which fields to match prior to any other index resolution and a WHERE clause will further narrow the result set.
For instance SELECT * FROM invoice LEFT JOIN customer ON invoice.custnum=customer.custnum.
These two options are not the same. The OF option does not impose any weight on the index it implies, it simply substitutes the fields in your predicate so you don't have to. The standard index selection rules apply here as if you had explicitly specified the fields in the predicate.
A query like the following might take a long time to resolve because the type field is indexed and we're doing an equality match on its only component. The compiler will choose this as the index for this query, which is unfortunate in this case because the custnum index only contains a couple of entries while type may contain thousands:
FOR EACH invoice OF customer WHERE invoice.type EQ 5:
The USE-INDEX option should be used ONLY when the need to specify a certain index can be truly justified by the business logic or some other consideration. Otherwise, the use of the USE-INDEX option is NOT recommended because it eliminates any opportunity for index bracketing and any possible index selection optimization.
Also, the use of explicit field matching is recommended over that of the OF option for readability, code stability and best index selection results.