Re: How to create a descending index on the primary key.

From: FaheemRao <>
Date: 13 Dec 2003 14:39:42 -0800
Message-ID: <>

Oracle treats desc indexes as function bases indexes .

First of all try to create a normal index (without desc) and see how oracle responds. I ecatly dont know why oracle is throwing that error . Check that table and index are in same schema . Following is an alternae solution.
I guess you are trying to use desc indexes for query using

order by column_name desc

Optimizer uses function bases index only if you use exact function in query.

you can create primary key with normal index and then create a desc index and then set parameter query_rewrite_enabled = true in session or system level and see explain plan to see if desc index is being used or not.
There are good chances if you use order by column_name desc that optimizer going to use desc index.


Faheem (Tuhin Kumar) wrote in message news:<>...
> Hi,
> Oracle give the error ORA-01418 when I try to do the
> following;
> Create unique index t1_pk on TABLE1(EntryId DESC) ;
> If the I try to add primary key Contraint using the above index t1_pk
> as below:
> USING INDEX t1_pk;
> the following error comes "ORA-01418: specified index does not exist"
> but the index t1_pk exists.
> My requirement is to create a descending index on EntryId which gets
> applied for all the queries.
> Thanks
> Tuhin
Received on Sat Dec 13 2003 - 23:39:42 CET

Original text of this message