Re: Delphi/Oracle Database Perfromance - Order By Problem

Well, I suppose better late than never, though I suspect the latter probably appeared likely. I apologize for my tardiness in answering.

The _Oracle7 Server Concepts Manual_, part number 6693-70-1292
(December 1992) discusses this on page 13-48. The topic is "ORDER BY
on Indexed Column". There are two requisites for optimization.

The ORDER BY must target a column of a single-column index, or a leading portion of a composite index. The index cannot be a cluster index.

There must be a PRIMARY KEY or NOT NULL integrity constraint that guarantees that at least one of the indexed columns listed in the ORDER BY clause contains no nulls.

I suspect the latter is the culprit here. I have tested this on version 7.1.4 and it appears to work as documented.

So, if you can ensure the latter on the TIME_ENTER column, the index should be used (i.e., there should be a NOT NULL constraint on the TIME_ENTER column, and an index must exist on the TIME_ENTER column. The index need not be UNIQUE).

I think the reason for the NOT NULL requirement is that rows with NULL values in the indexed column are not indexed, and thus retrieval for a
(conceptual) full table scan cannot be guaranteed.

Please contact me if this does not work. It may be possible to fool the optimizer into the desired behavior using a gratuitous WHERE clause targeting the TIME_ENTER column.

Regards,

Gary

-- 
__________________________________________________________
Gary Gapinski                email: Gapinski_at_lerc.nasa.gov
NASA Lewis Research Center   voice: +1 216 433 5251