Re: full table scan cheaper than index??

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 Received on Sat Jul 21 2001 - 16:33:27 CDT