Re: Oracle 9.0.1.6 fails to use index with '=' but will with 'like'

From: <steven.herod_at_gmail.com>
Date: 17 May 2005 07:27:45 -0700
Message-ID: <1116340065.019866.305620@f14g2000cwb.googlegroups.com>

Thanks for your amazing answer.

We found the fix, in short, the problem is a database which isn't
'normal'

What we did was on each of the affected columns in the affected tables we have issues an 'alter table modify <column> <type>' command to redefine the column as the same type and size it already was.

Amazingly, this now provokes the CBO to pick the index even with the
'=' clause (as you would hope)

We believe this issue relates to corruption in the catalog or something similar, our dba has discovered a number of system views which he says are invalid and this is in no way normal.

I should explain this problem happened after a server move and an upgrade from 9.0.1.4 to 9.0.1.6

We will be chasing this up with TAR to oracle with a whole heap of detail, but thank you so much for your answer! Received on Tue May 17 2005 - 09:27:45 CDT