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

Hi Farheem,

           Thanks for the response. I tried with ALTER SESSION set QUERY_REWRITE_ENABLED=TRUE; but the explaun plan still picked up the primary key index. If I drop the primary key constraint from the table, the it do full table scam but doesn't use the index t1_pk.

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
>
>
Received on Mon Dec 15 2003 - 09:10:48 CET