Kbase 41697: Index is not used when running SQL-92 query containing Inner joins even after running UPDATE STATISTICS
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/05/1998 |
|
Solution ID: P11697
FACT(s) (Environment):
Progress 9.1D
SYMPTOM(s):
Index is not used when running SQL-92 query containing Inner joins even after running UPDATE STATISTICS
UPDATE STATISTICS has been executed before running the Query
Example of a query that shows the issue:
SELECT pub.obasic."case-no", pub.obasic."ord-no",
pub.obasic."start-date", pub.obasic."start-time", pub.obasic."pf-dept",
pub.epiordidx.episode_id, pub.epiordidx.grp_id
FROM pub.episode
INNER JOIN pub.epiordidx
ON pub.epiordidx.episode_id = pub.episode.episode_id
INNER JOIN pub.obasic
ON pub.obasic."case-no" = pub.epiordidx.case_no AND
pub.obasic."ord-no" = pub.epiordidx.ord_no
WHERE pub.episode.episode_id = 2
Table scan of obasic is made, primary index case-no,ord-no is not used
With prior version of 9.1D, after running UPDATE STATISTICS, the related Index is used
CAUSE:
Run extra statement to make use of related index.
FIX:
Run UPDATE INDEX STATISTICS; COMMIT;
Documentation is available from readme.pro file for 9.1D