Re: Index suppression

If you want a sensible answer to a question like that\ you need to supply (as a minimum):

    The sql statement - showing the modification

    The two execution plans as generated by a     call to dbms_xplan.display() or by a direct     query against v$sql_plan - including the     filter_predicates and access_predicates     columns.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

• Original Message ----- From: <genegurevich_at_discoverfinancial.com> To: "oracle-l" <oracle-l_at_freelists.org> Sent: Thursday, August 03, 2006 9:40 PM Subject: 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
>
>

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