Re: full table scan cheaper than index??

Jan Haase <jh_at_informationsdesign.de> wrote in message news:<3B4DAB35.4D695F4F_at_informationsdesign.de>...
> Hi Chuck.
>
> Thanks for your fast reaction. :)
>
> > >I've got a serious problem: Yesterday I found out that some queries to a table
> > >were very slow, so I gave the "analyze"-command (estimate) to get new
> > >information about the data.
> > >Now the optimizer seems to be completely nuts, it doesn't use any index at
> > >all!
> > >"Set autotrace on" tells me that the cost of using an index is much higher
> > >than a full table scan.
> > >
> > >The database (Oracle 7.3.4) runs in "CHOOSE"-Mode, so it uses cost-based
> > >analysis.
> > >I used "analyze table xxx estimate statistics sample 10 percent;" and another
> > >"analyze"-command for the index to be used didn't change oracle's behaviour.
> > >Even deleting the analysis-data (by typing "analyze table xxx delete
> > >statistics") didn't help.
> >
> > Full scans on joined tables is frequently faster than index scans. It
> > depends on how many rows you're trying to retrive, and how many are in
> > the table. Generally speaking, if you're retrieving more than 10% of
> > the rows in a table, a table scan is faster because it's sequential
> > and do larger io's. If you really want it to use the index, hint the
> > query, or try reducing db_file_multiblock_read_count. That'll favor
> > indexes a little more.
>
> The problem is, the database database-performance was ok until I used "analyze".
> An example for a daily used query:
>
> select id, timestamp
> from my_table
> where timestamp > to_date('20010701','YYYYMMDD');
>
> The table "my_table" has an index, let's call it "my_index", on the column
> "timestamp".
>
> This should be the best-case for the query: An index exists just on the used column
> in the "where"-part.
> But Oracle thinks that a full table scan is cheaper than using this index. The full
> table scan has a cost of about 20000, the index has a cost about 50000.
>
> Any more ideas?
>
> Thanks in advance,
> Jan

Try:
set timing on
select /*+ INDEX(A my_table_idx) */

     id, timestamp
from my_table A
where timestamp > to_date('20010701','YYYYMMDD');

Compare the run times and compare the plans to see if Oracle used the index. You could also try re-analysing with a larger sample size before doing this and do the comparision.

• Mark D Powell --