Re: multi-column indexes, statistics and selectivity

From: Jonathan Lewis <>
Date: Fri, 11 Dec 2009 15:49:52 -0000
Message-ID: <>

"Mark D Powell" <> wrote in message

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)


Jonathan Lewis
Received on Fri Dec 11 2009 - 09:49:52 CST

Original text of this message