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

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

Faheem

tkumar_at_ipolicynet.com (Tuhin Kumar) wrote in message news:<e4ad76f5.0312130556.63570b37_at_posting.google.com>...
> 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:
> ALTER TABLE TABLE1 ADD CONSTRAINT TABLE1_PK PRIMARY KEY (EntryId)
> 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