Re: multi-column indexes, statistics and selectivity

"Mark D Powell" <Mark.Powell2_at_hp.com> wrote in message news:80408c17-9e36-4602-a787-94f017a1edcc_at_g23g2000vbr.googlegroups.com...

To add to what others have said, how the CBO treats the two columns is going to in part depend on how the sql statement is written. If the query uses two bind variables, one for each indexed column, in the where clause conditions then the CBO (ignoring bind variable peeking) uses the default selectivity (.05) for the first column times the default selectivity of the second column in its calculations.

Mark

That 5% figure does appear in various places in the optimizer's arithmetic, but in this case is would be using the product of 1/num_distinct for the two columns, or 1/distinct_keys for the index (depending on version)

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com