Kbase P133562: Oracle DataServer slow performance using find or for first with non unique indexes
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
9/10/2008 |
|
Status: Unverified
SYMPTOM(s):
Oracle DataServer slow performance using find or for first with non unique indexes
FACT(s) (Environment):
Where clause not used.
Oracle access plan shows table scan for the query that Oracle DataServer does to read the progress_recid using the order by clause.
All the field in the candidate index allow null values.
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
Oracle DataServer
Oracle 9i
Oracle 10g
CAUSE:
Enhancement Request# OE00172169
CAUSE:
In spite of the index that has all the fields to make the sort, Oracle does not use it, and makes a full table scan. The reason, is because Oracle behavior is different from Progress when comes to storing index entries with null values. Before put the entry in the index, Oracle evaluates if at least one of the fields has a non-null value, otherwise that entry will not be put in the index. So, as side effects, whenever a query is executed, Oracle looks to fields of the most appropriated index, and check if at least one of those fields has a not null restriction. If all of the fields allows null, Oracle can not be sure it will read all the records the application asked using the index, and has to make a full table scan.
FIX:
This Enhancement Request has not been implemented in the product.
As a workaround, change the progress_recid in Oracle to not null, which will include all indexes in the rule for not-null, and allows find first use-index run faster.