Kbase P117635: SQL-92: Does the order of the predicates in the WHERE clause determine index selection?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
16/11/2009 |
|
Status: Verified
GOAL:
Does the order of the predicates in the WHERE clause contribute to index selection?
GOAL:
Does the order of the predicates in the WHERE clause determine the "top three" fields to use in index resolution?
GOAL:
Is it true that adding another field that's a component of an index to a predicates can take it from using a "good" index to doing a full table scan?
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.x
FIX:
No, all predicates are considered for use in index selection. The order is which the predicates appear in the query is not considered in index resolution and does not contribute to index selection.
No, it NOT true that adding another index component to a predicates can take it from using a "good" index to doing a full table scan. On the contrary, Adding a new predicate on another index component can help if the additional component can be used with the components already in the query. For example, if the query already has predicates giving values for index components #1 and #2, then adding a predicate for component #3 can help when that index is the best index to search on.
The most important thing for optimizing the index selection process is to create index statistics:
UPDATE INDEX STATISTICS FOR <some table> ;
because that will let the SQL-92 Optimizer most accurately compute the best predicates and indexes to use.