When does CBO use index vs. FTS?

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 Thu Mar 29 2001 - 23:48:12 CST