Re: When does CBO use index vs. FTS?

Howard is right as usual. Jonathan Lewis would know. One thing to consider is that your histogram hint won't be used if your query has bind variables - something that's often desirable to reduce re-parsing.

Van

"Doug C" <dcowles_at_i84.net> wrote in message news:hs3bctc2cjjr9fmfqi2u166d19jeecbhqi_at_4ax.com...
> You hit the nail on the head, only there's a problem. I'm only selecting
 only
> the indexed column. I would think therefore that the query could be
 satisfied
> by the index by itself, but that's not what Oracle is doing - it is doing
 a full
> table scan. I can make it use the index with a histogram or a hint, but
 I'm
> just very curious as to why it is doing a FTS. There must be something
 in it's
> calculated statistics that's telling it the index is a waste of time, but
 what?
> How can I see that? The index is not perfectly contiguous, but what is
 the
> signal to Oracle, and what is it's value?
>
> - D
>
>
> On Sat, 31 Mar 2001 13:09:41 +1000, "Howard J. Rogers" <howardjr_at_www.com>
 wrote:
>
> >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 Sun Apr 01 2001 - 12:26:24 CDT