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

From: Tuhin Kumar <tkumar_at_ipolicynet.com>
Date: 15 Dec 2003 01:09:15 -0800
Message-ID: <e4ad76f5.0312150109.4f1289e5_at_posting.google.com>

Hi Faheem,

           Thanks for the response.
           I tried using ALTER SESSION set QUERY_REWRITE_ENABLED=TRUE; but still

the primary key index was picked up. When I dropped the primary key constraint from the table, the explain plan showed doing the full table scan.

Thanks,
Tuhin

faheemrao_at_yahoo.com (FaheemRao) wrote in message news:<43b58913.0312131439.5cc4a10b_at_posting.google.com>...
> 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 Mon Dec 15 2003 - 10:09:15 CET