Kbase P112529: The SQL-92 engine is using the wrong index when the fields in the WHERE clause are part of a primary
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
18/08/2010 |
|
Status: Verified
SYMPTOM(s):
The SQL-92 engine is using the wrong index
All fields in the search condition of the SELECT query are part of a primary unique index
The SQL-92 optimizer uses an index which does not contain all the fields in the query's WHERE clause
The database tables have been created via 4GL
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
OpenEdge Category: SQL
CAUSE:
If an index has many key components (> 3), and there are no STATISTICS, then the SQL cost estimator can only use its built-in selectivity estimates, and compute the index overall selectivity based on the laws of probability, which say multiply together the selectivities of the individual predicates. The end-result of this can be that an index with many key components looks as good as a unique index. In other words, the unique index and the non-unique index produce similar cost estimates, because there are no STATISTICS in the database.
FIX:
Run UPDATE INDEX STATISTICS or UPDATE STATISTICS on the table(s) which is (are) referenced in the SELECT query.