Index suppression

  • From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 3 Aug 2006 15:40:15 -0500

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

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

thank you

Gene Gurevich


Other related posts: