Kbase 32875: SQL statement containing a WHERE clause with multiple JOINS causes the SQL-92 engine to do a table scan
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/05/1998 |
|
Solution ID: P2875
FACT(s) (Environment):
Progress 9.1C
Progress 9.1D
SYMPTOM(s):
SQL statement causes a table scan.
A specific SQL statement containing a WHERE clause with multiple JOINS causes the SQL-92 engine to perform a table scan.
Appropriate index exists for the table.
CAUSE:
Bug# 20020501-001
CAUSE:
The reason SQL is doing a table scan, and not using the index referred to, is that SQL thinks the table scan and related dynamic index are less costly than using the index. SQL thinks this because the code that estimates costs is seriously underestimating the cost of dynamic indexes.
The fix, to be included in Progress, is a rewrite of the cost estimation code (the cost model) for dynamic indexes. The new cost model more accurately computes the dynamic index cost, for this and similar cases, as more expensive than using the database index.
FIX:
Apply Progress patch 9.1D01 or higher.