Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: full table scan cheaper than index??

Re: full table scan cheaper than index??

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 21 Jul 2001 21:33:56 GMT
Message-ID: <tkrmms8nj9qn64@beta-news.demon.nl>

"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
>
>

Full table scans are cheaper because db_file_multiblock_read_count blocks are read ahead. It may have an excessively high value in your situation. db_block_size * db_file_multiblock_read_count should equal 64k in most cases.
You can influence the optimizer by lowering the parameter (it can be set on session level so it is safe to experiment)

Also: what is the value of v733_plans_enabled If it is FALSE change it to TRUE on session level and see what happens.

Another question: did you try to set event 10053 to follow the various methods the optimizer considers?

Finally: how many rows (ie which percentage of the table) does the query return?

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Sat Jul 21 2001 - 16:33:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US