Re: When does CBO use index vs. FTS?

There is a general rule of thumb that any time you want to retrieve more than about 2% of the records from a table, a full table scan will be issued. That's rather a lower percentage than many people imagine (and various Oracle material mentions slightly different figures -the Performance Tuning course materials mention 5%, for example. The point is that all the percentages are that sort of magnitude. Nothing like the 30% figure I saw you mention in your first post).

As to your particular calculation, though I haven't looked at it in great detail, it appears that you are ignoring the fact that the index is only a pointer to the rows in the table. So whilst it might be cheap to read the index, if you're going to have to visit most of the table afterwards anyway, you might as well scan *just* the table in the first place.

Of course, if your query could be satisfied entirely within the index itself, the optimizer is going to go for the index access path every time.

Regards
HJR "Doug C" <dcowles_at_i84.net> wrote in message news:5v9act4d4foill3qbiqin8ucloa9c5q122_at_4ax.com...
> Right - but how?
> The number of blocks in the table is 4429. I would think this would make
 for
> about 554 logical i/o's since multiblock read count is set to 8. As to
 the
> index, some values are as follows:
>
> distinct_keys 6
> avg_leaf_blocks_per_key 89
> avg_data_blocks_per_key 709
> clustering_factor 4254
>
> So was thinking maybe it would take clustering factor / distinct keys,
 which
> comes out to 709 blocks. But then that's less that 100 logical i/o's, so
 I
> would think it would take the index - but it doesn't. What is the
 calculation
> it is doing to decide which is better?
>
> - D
>
> On Fri, 30 Mar 2001 09:43:25 +0200, "Sybrand Bakker"
 <postbus_at_sybrandb.demon.nl>
> wrote:
>
> >No secret nothing unusual.
> >
> >It just tries to estimate IO needed in terms of blocks.
> >Whatever results in the least I/O is being used.
> >Based on the distribution of the data the estimate may of course be
> >incorrect.
> >
> >Hth,
> >
> >Sybrand Bakker, Oracle DBA
> >
> >"Doug C" <dcowles_at_i84.net> wrote in message
> >news:tr68ctkl25ibfsobsas92d0p3hcbuteeou_at_4ax.com...
> >> Is there a hard and fast rule for when CBO will use an index vs. a FTS?
> >> For example, if I am selecting a value from an indexed column
 (not-unique),
> >> is there any absolute scientific way to tell whether Oracle will pick
 an
 index
> >> or an FTS?
> >>
> >> I tried clustering_factor vs. blocks in table.. no go..
> >> I thought of selectivity, in terms of (distinct values/ # of values),
 and
 I
> >> can't determine the specific threshold.
> >>
> >> I've heard rules of thumb for quite some time such as whenever Oracle
 "thinks"
> >> it's going to return 30% of the rows or more it will go for the FTS..
 But
 ... do
> >> we really know? Is this some super secret proprietary thing? Or, can
 I
> >> actually garner a specific selectivity factor that will allow me to say
 with
> >> confidence - If I put one more row in that table, it will switch from
 index to
> >> FTS..
> >>
> >> I'm working on 8.1.6 AIX 4.3.3.
> >>
> >> Data distribution is very un-selective...
> >>
> >> As follows -
> >>
> >> Table - Somestuff
> >> SomeIndexedColumn Count
> >> A 32
> >> B 121
> >> C 8
> >> D 4
> >> E 1
> >> F 8
> >> G 1
> >> H 2648
> >> I 12099
> >>
> >> Pretty un-selective index.. but clearly, selecting value 'I' would be
 better
> >> with a full table scan, and most of the others would be better with an
 index.
> >> But without a histogram, Oracle doesn't know or understand this skewed
> >> distribution, so what, under the covers, does Oracle do to make this
 choice.
> >>
> >>
> >> I *don't* have a problem.. I'm not trying to figure out how to make
 Oracle
 use
> >> an index, I know about bitmaps, etc., I can make it do what I want
 with
 hints
> >> etc., That's not the question...
> >>
> >> The question is .. what's the secret formula? Is it visible to me?
> >> Just a research/interest question only..
> >>
> >> Is it so proprietary and super-secret that no one knows?
> >>
> >>
> >> Thanks,
> >> D
> >>
> >
>
Received on Fri Mar 30 2001 - 21:09:41 CST