Re: multi-column indexes, statistics and selectivity
Date: Fri, 11 Dec 2009 15:49:52 -0000
"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.
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.comReceived on Fri Dec 11 2009 - 09:49:52 CST