This content has been marked as final. Show 2 replies
The TEXTFIELD1 column is the leading column of the index.
As you have an IN clause (equivalent to OR conditions) on this column, Oracle must therefore probe the index 10 times to get the rows from the 10 possible values of interest. It can use the TEXTFIELD2 value within the index each time, but it needs to access the index 10 times.
Doing so is deemed by the CBO to be more expensive than not using the index. But only just, because if you only have to use the index 9 or less times, then it's the cheaper alternative.
In the other environment, the statistics or data is just different enough for the CBO to use the index.
Depending on how many distinct values you have for TEXTFIELD2, it might be more index friendly if you had the columns the otherway around in the index.
In another environment with same table, index and statement, it uses the index..
it is not necessary to get the same explain plan on all the servers unless you have the same statistics across the servers.
Post the explan for your sql statements for both the cases and the last analyzed details for that.
What is your Oracle Version.