multi-column indexes, statistics and selectivity
Date: Fri, 04 Dec 2009 16:48:43 +0000
Message-ID: <i7ednZdyeur2oITWnZ2dnUVZ7txi4p2d_at_brightview.co.uk>
Can anyone tell me (or point me at information) about how statistics are generated/used for multi-column indexes?
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.
This would make a big difference in our app, since our major/minor status (in practice) have interesting and complex correlations.
BugBear Received on Fri Dec 04 2009 - 10:48:43 CST