Kbase P133558: Oracle DataServer slow performance using find or for first and without where clause
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/03/2009 |
|
Status: Unverified
SYMPTOM(s):
Oracle DataServer slow performance using find or for first and without where clause
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.
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
Oracle DataServer
Oracle 9i
Oracle 10g
CAUSE:
In spite of the index having 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 adding the entry in the index, Oracle evaluates if at least one of the fields has a not null restriction at the field definition, otherwise that entry will not be added in the index. So, as side effect, whenever a query is executed, Oracle looks to the 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:
Make sure at least one of the fields for Progress primary index to have a mandatory clause, so it will be converted to not null in Oracle.
In case this is not possible, a workaround for it could be:
a) create a a field in the table, that will be used for nothing, and define it as not null and a default value for.
b) add this this field as the last component for the Index that represents the Progress original primary key. That is the only index that does not have the Progress_recid column.