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: Jan Haase <jh_at_informationsdesign.de>
Date: Sat, 21 Jul 2001 21:35:39 GMT
Message-ID: <3B4EE498.C2148401@informationsdesign.de>

Hi Sybrand.

Thanks for your reply. :)

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

db_file_multiblock_read_count is set to 16, block size is 8k.

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

It's set to FALSE.

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

No, I didn't. How do I set it and what does it do?

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

About 50 of 12 millions.

-

I tried two queries, here are the results:

Q1: select count(*) from gnd_storm;

Q2: select distinct id from gnd_storm where

   stb <= TO_DATE('01/03/2001','DD/MM/YYYY') and    ste >= TO_DATE('01/03/2001','DD/MM/YYYY') + 1;

There are three indexes:

pk_gnd_storm (ssq_nr, osq_nr) (primary key)
ix_gnd_storm_sta (sta) (nonunique)
ix_gnd_storm_ste (ste) (nonunique)


........................................................................

Q1:
normal execution:

Elapsed: 00:02:32.60

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20398 Card=12341513)    1 0 SORT (AGGREGATE)
   2 1 TABLE ACCESS (FULL) OF 'GND_STORM' (Cost=20398 Card=12341513)

........................................................................

-> changed v733_plans_enabled to TRUE

Elapsed: 00:00:39.00

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4801 Card=12341513)    1 0 SORT (AGGREGATE)

   2    1     INDEX (FAST FULL SCAN) OF 'PK_GND_STORM' (UNIQUE) (Cost=
          4801 Card=12341513)

That's much better. But: A select count(*) should work in about a second, it could just give the cardinality of the index. Or...?

........................................................................
........................................................................
Q2:
normal execution:

51 rows selected.

Elapsed: 00:03:30.28

Execution Plan


   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=24898 Card=218630 By
          tes=7433420)
   1    0   SORT (UNIQUE)
   2    1     TABLE ACCESS (FULL) OF 'GND_STORM' (Cost=20398 Card=2186
          30 Bytes=7433420)

........................................................................
-> used with hints: /*+ INDEX(GND_STORM, IX_GND_STORM_STB */

51 rows selected.

Elapsed: 00:20:32.28

Execution Plan


   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=190826 Card=218630 B
          ytes=7433420)
   1    0   SORT (UNIQUE)
   2    1     TABLE ACCESS (BY ROWID) OF 'GND_STORM' (Cost=186326 Card
          =218630 Bytes=7433420)
   3    2       INDEX (RANGE SCAN) OF 'IX_GND_STORM_STB' (NON-UNIQUE)


This index seems to be very bad - but why? I dropped and recreated it, so now it has a cost of 186000 - before it had a cost of about 7 millions!

........................................................................
-> changed v733_plans_enabled to TRUE

(no change, like full table scan above)

........................................................................
........................................................................

Obviously the full table scan is indeed cheaper than the use of the index. But why?
Do you have any idea?
What can I do to reorganize the indexes so that the database uses them like it did before? Some days ago - before I used "analyze" - it needed about one minute for Q2, without using any hint.

Thanks in advance,

Jan Received on Sat Jul 21 2001 - 16:35:39 CDT

Original text of this message

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