Re: EXPLAIN PLAN : better with RULE!

Hi Richard!

I know all of that!!
However, the query which explain plan is in my first message run very faster with RBO than with CBO, and THAT is my "problem"! ... As I know that FTS is better than use of an index with poor selectivity... ;-)

I thank you all the same for this lesson, I am sure that it can take advantage to somebody!

Cordially.

"Richard Foote" <richard.foote_at_bigpond.com> a écrit dans le message news: 9bTY8.36123$Hj3.107818_at_newsfeeds.bigpond.com...
> Hi Epicentre Team B Annecy,
>
> You are making a common but often erroneous assumption that the use of an
> index is *always* better than a FTS. Fortunately the CBO does not make
this
> assumption, hence why indexes may not be used.
>
> Now I have always had a soft spot for the little battler and I an official
> member of the "Hey, Stop Picking On The Poor FTS, It's A Better Access
Path
> Than Many Give It Credit For Society", or HSPOTPFTSIABAPTMGICFS or short
:)
>
> A few key points.
>
> If by accessing an index to read x number of rows is *more expensive* than
> to use a FTS, then a FTS my friend is the way to go. The FTS has three
> really really big advantages over an index.
>
> 1) A conventual read of an index can *only* access data one block at a
time.
> Each read of an index block, one logical I/O, each subsequent access to
the
> table, one logical I/O. A FTS can read multiple blocks in one I/O. This is
> set (and tuned) via the db_file_multiblock_read_count parameter but lets
say
> it's set to 8. This means it can effectively read the table in 'blocks
under
> the HWM/8' logical I/Os. Very important point.
>
> 2) Each block in a table is only ever accessed *once* during a FTS. When
> accessing via an index, a bock may be visited many times (up to the number
> of rows that reside in the block). Each time it needs to be reaccessed is
an
> additional logical I/O. In theory, if you were to read every row via an
> index, the number of logical I/Os would be 'the majority of blocks in the
> index + the number of rows in the table'. Very important point. A FTS is
> remember 'number of *blocks*/8'
>
> 3) A FTS can be performed in parallel while a conventual index scan can
not.
> Assuming we have an effective degree of parallelism of 8, this means we
can
> read an entire table in approximately the same time it takes an index to
> read 'number of blocks/8/8' I/Os. Very very important point.
>
> The question the CBO asks is what is the break even point. At what point
is
> it more efficient to use an index, at what point is it more efficient to
> read the whole damn lot. This of course depends. Not so much on the
> 'percentage of rows' that need to be accessed, but more so on the
proportion
> of 'blocks' that one method uses over the other based on the above three
> points.
>
> If accessing one row, then the cost of the index is say root block+branch
> block+ leaf block in the index + one data block. Four logical I/Os
(although
> the CBO might be clever enough to assume some of the index blocks may
> already be in memory). To read the table via FTS is say 1000 table blocks
/8
> or 125 logical I/Os hence the index wins.
>
> To read say 10/% of the table requires say, one leaf block+one branch
block+
> say 100 leaf blocks + say 1000 table blocks (ie. approx 10 rows per block)
> which equals 1102 logical I/Os. To read the table via FTS is say 1000
tables
> blocks / 8 or 125 logical I/Os (again). Hence, FTS wins and wins easily.
>
> If tables are not analyzed (and therefore Oracle has no statistics) how
can
> Oracle compute the above calculations. It can't. In fact what Oracle does,
> is assume the tables are really small, tiny, not deserving of statistics.
So
> small in fact that they can be read with one logical I/O (ie. are less
than
> the db_file_multiblock_read_count of blocks). *Note one logical I/O to
read
> this table* (which is far better than 2 logical I/O that the best index
can
> offer !!). That's why the CBO ignores indexes on non analyzed tables.
>
> So in summary, although you say the CBO is not using the indexes and that
it
> should be aware of them, note it *is* aware of them but it determines that
> it's less expensive to read the whole table anyway.
>
> I could go on but I hope this all makes sense and helps in your
> understanding of what might be going on.
>
> Good Luck
>
> Richard
>
>
> "Epicentre Team B Annecy" <carmanet_at_epicentre.fr> wrote in message
> news:ah0qqj$9oq$1_at_wanadoo.fr...
> > I know the influence of statistics for CBO,
> > and I know that when no stats, CBO makes join from left to right
> > in the FROM clause,
> > but is CBO not supposed, when no stats, knowing at least that indexes
> exist
> > on tables?
> >
> > Thanks again.
> >
> > "Loadrunner" <load_at_runner.com> a écrit dans le message news:
> > 1026810930.9839.0.nnrp-10.c2d92da9_at_news.demon.co.uk...
> > > Can you confirm that you have analyzed tables with the "For Table For
> All
> > > Columns For All Indexes" clause?
> > >
> > > If very large table estimate the statistics.
> > >
> > > The explain plan produced when CBO active can vary dramatically with
or
> > > without statistics.
> > >
> > >
> >
> >
>
>
Received on Tue Jul 16 2002 - 07:03:12 CDT