Re: full table scan cheaper than index??

On Fri, 13 Jul 2001 14:07:52 +0200, Jan Haase <jh_at_informationsdesign.de> wrote:

>
>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...?

There is no ordinality information maintained - it would cause serialisation of activity on the index. The entire index is scanned to count the number of rows in the table. The index is physically smaller than the table therefore it is faster than a table scan.

>
>........................................................................
>........................................................................
>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?

Same reason. Your query is something like get me the ids of all the rows in gnd_storm which describe something that began (I'm guessing that's what b is) before 1/3/2001 and ended after 1/3/2001 ie what items were active on this date. The problem is that there are only two ways in which Oracle can perform the search. It either gets all the rows with an stb before the input date and checks the ste of each or it gets all the rows with an ste greater than the input date and checks the stb of each. If half the rows have an ste greater than the input date, half the rows in the table must be inspected which is better done by scanning the table than by getting each entry from the index and looking up the individual rows in the table one by one.

This type of query is always problematic. Oracle have a time series option, I think, which may help. Alternatively, you could maintain a table containing ids and the dates on which they were active although you would have ensure the integrity of this derived data.

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

Delete the statistics from the table or you could try a "rule" hint

select /*+ rule */ 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; Received on Sat Jul 21 2001 - 16:37:52 CDT