Re: When does CBO use index vs. FTS?

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 - 18:51:30 CST