Index suppression

Hello everybody:

I stumbled upon a SQL which executes in 20 to 30 sec the way it has been
written. If I however suppress one index
by replacing "table1.col1 = table2.col1" with "table1.col1+0 =
table2.col1", the query executes in under 0.1sec.
The explain plan shows that in the original version oracle accesses table1
via a primary key and then reads
data from the table. In the modified version oracle does a full table scan.
As the table1 only has 10000 rows,
full table scan is probably faster than an index read followed by the table
read.

So I wonder why does oracle optimizer (my version is 9204) insists on using
the index and how can I force
it to be smarter. My optimizer_index_costr_adj is 100, which makes index
as expensive as possible (I think).
I can't change the multiblock_read_count. Is there anything else I am
missing?


thank you

Gene Gurevich


--
http://www.freelists.org/webpage/oracle-l


Other related posts: