Re: The Cost Based Optimizer drives me crazy...

Esmé Venter >>
This statement (an example) makes use of the index on column1:

        Select * from table A where column1='xxxx' This statement does NOT make use of the index on column1:

        Insert into table B (select * from table A where column1='xxxx') Why would this happen? <<

Check the statistics and counts for the two tables in question. If the number of rows in all_tables is not close to the actual count then update the statistics: analyze owner.table_name estimate statistics sample nnnnn rows; (command only works properly for 7.1.6 and up) then redo the explain plans.

If table b is small it may be quicker to sequentially read the table rather than use the index!

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Mon Sep 08 1997 - 00:00:00 CDT