Re: When does CBO use index vs. FTS?

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 Sat Mar 31 2001 - 02:11:10 CST