Re: When does CBO use index vs. FTS?

This distribution would, as you already know, benefit from a histogram on the column.

For your testing purposes, try setting your optimizer_goal or optimizer_mode between FIRST_ROWS and ALL_ROWS. Then try different combinations of these with a test query that DOES and DOES NOT use bind variables in the WHERE clause.

-Kevin
"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 - 11:47:42 CST