Re: multi-column indexes, statistics and selectivity
Date: Fri, 11 Dec 2009 15:43:16 -0000
Message-ID: <K-udnUU8QOwQ9b_WnZ2dnUVZ8rSdnZ2d_at_bt.com>
"bugbear" <bugbear_at_trim_papermule.co.uk_trim> wrote in message news:i7ednZdyeur2oITWnZ2dnUVZ7txi4p2d_at_brightview.co.uk...
> We have a 2 column status model (major and minor status, if you like).
> This is key to our application.
>
> "Naturally" we have put a 2 column index on these two columns.
>
> But we have "doubts" about whether the CBO is always
> doing as good as job as we'd like.
>
> The key question seems to be:
>
> Are the columns "treated as a pair" so that the
> frequency estimate for a pair of statuses depends
> on both of them or...
>
> Are the columns treated in isolation
> so that the frequency estimate
> is simply obtained by getting the estimate
> for each column, and combining them mathematically.
>
If you create histograms on the two columns separately and use literals in the query, then Oracle can use the histograms to calculate the selectivity of the two columns separately and multiply to get the overall selectivity. If you are using bind variables, then the values used for this optimisation step will be the peeked bind values on the first call - and that may not be good for subsequent calls).
For a pair of status columns with (we assume) a small number of distinct values, it makes sense to use literals and histograms. (And then it's a good idea to construct histograms, rather than letting the automatic stats collection gather them - see: http://jonathanlewis.wordpress.com/2009/05/28/frequency-histograms/
If you don't have histograms, then there are various possibilities depending on version of Oracle. The optimizer may multiply the column selectivities, but it may base its arithmetic on the number of distinct keys in the index. The behaviour varies between 10.2.0.1, 10.2.0.3 and 10.2.0.4.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.comReceived on Fri Dec 11 2009 - 09:43:16 CST