Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: EXPLAIN PLAN : better with RULE!

Re: EXPLAIN PLAN : better with RULE!

From: Epicentre Team B Annecy <>
Date: Tue, 16 Jul 2002 14:03:12 +0200
Message-ID: <ah120h$eob$>

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!


"Richard Foote" <> a écrit dans le message news: 9bTY8.36123$
> 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
> 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
> 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
> Each read of an index block, one logical I/O, each subsequent access to
> 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
> it's set to 8. This means it can effectively read the table in 'blocks
> 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
> 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
> Assuming we have an effective degree of parallelism of 8, this means we
> 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
> 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
> 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
> 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
> or 125 logical I/Os hence the index wins.
> To read say 10/% of the table requires say, one leaf block+one branch
> 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
> 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
> 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.
> small in fact that they can be read with one logical I/O (ie. are less
> the db_file_multiblock_read_count of blocks). *Note one logical I/O to
> this table* (which is far better than 2 logical I/O that the best index
> 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
> 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" <> wrote in message
> news:ah0qqj$9oq$
> > 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" <> a écrit dans le message news:
> >
> > > 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
> > > without statistics.
> > >
> > >
> >
> >
Received on Tue Jul 16 2002 - 07:03:12 CDT

Original text of this message