Re: Index by Date Descending: Ignored

Unfortunately there are several optimizer paths that cease to be available when there is a descending column in an index definition.

I haven't tested this one, but perhaps it is just another example of incomplete implementation.

I note from another post that you have tried the index_desc hint, have you also tried a simple index() hint ? (the data is actually massaged then stored in ascending order
so you don't want to use it in descending order).

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

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


>This seems like a basic problem, but a search of the newsgroup yielded

>no similar answer.

>

>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.

>

>Does it have anything to do with he fact that I create the command

>using:

>   CREATE INDEX USER_CREATE_DATE_I ON

>     USERS(record_create_date DESC) ;

>

>But if I look at the script for the index (via the Toad schema browser)

>I see:

>   CREATE INDEX USER_CREATE_DATE_I ON

>     USERS(SYS_NC00079$);

>

>Any suggestions?  What obvious point am I missing?

>

>

>thanks,

>

>bill milbratz

>

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.