Re: Index by Date Descending: Ignored

From: Hans Bos <hans.bos_at_xelion.nl>
Date: Wed, 18 Oct 2000 21:45:09 +0200
Message-ID: <8skupu$646$1@porthos.nl.uu.net>

Hello,

Perhaps this is because the record_create_date may contain null values.

For some reason oracle doesn't store null values into an index. This means that indexes cannot be used to sort columns that may contain null values.

If you add the "not null" constraint to the definition of the record_create_date column, an index can be used for sorting.

Greetings,
Hans Bos.

<billmil_at_my-deja.com> wrote in message news:8s52il$v1n$1_at_nnrp1.deja.com...

> I have an index on a "users" table by a date field
> (column "record_create_date") in descending order. We're running
> 8.1.5.0.2
>
> They query "SELECT * FROM USERS ORDER BY RECORD_CREATE_DATE DESC"
> *never* uses this index.
>

...
> Any suggestions? What obvious point am I missing?
>
>
> thanks,
>
> bill milbratz
>
Received on Wed Oct 18 2000 - 14:45:09 CDT