Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: EXPLAIN PLAN : better with RULE!
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.
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 - 06:17:38 CDT