Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: full table scan cheaper than index??
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:
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
![]() |
![]() |