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

Daniel,

Thats right query_rewrite_enabled= true is also concerned with matrialized views , but it also affect optimizer to choose function based index becuse when you create function based index orale pre-calclate the value of that function and store in index. Now when you use exactly same function in SQL optimizer uses that index, but it has to be that excat same function.

Tuhin my guess is that you are using desc index becasuse you need to get your sql results in desc order(let me know If I am wrong), for that you have put

"order by column_name desc" in sql to make optimizer take advantage of function-based index.  

Now sometimes even after setting query_rewrite_enabled= true optimizer does not use functiobased index.
There is a solution to that there is an other parameter the name of which is not on top of my head. the default value of that paramet is 100 which means that optimizer think if it going to use function based index coast going to be 100 which is not the case in real. you have to set the value of that parameter less than 100. I will do a search for you to find excat name of that parameter.

Faheem

danielroy10junk_at_hotmail.com (Daniel Roy) wrote in message news:<3722db.0312150714.24f0e0f9_at_posting.google.com>...
> Guys,
> I was able on my 9R2 version to reproduce this ORA-01418 error. It
> looks like Oracle doesn't "see" the index when creating the PK
> constraint, if this index was created with the DESC option. I think
> that a more meaningful error message in that scenario would be that
> Oracle can't use a function-based index to enforce a PK constraint.
> Note also that as far as I know, the parameter query-rewrite_enabled
> applies to MV's, which are not in the picture in this case. The CBO
> should use the DESC index if appropriate, no matter what the
> query_rewrite_enabled parameter is set at (sorry, I don't have any
> Oracle to test that at the moment). Could the OP please post the
> query, create a DESC index, and then post the execution plan, and we
> could start from there in trying to tune it.
>
> Daniel
Received on Wed Dec 17 2003 - 04:38:14 CET