multi-column indexes, statistics and selectivity
in [Oracle]
|
Prev: createing db 11gr1 on grid 11gr2
Next: Documentation
From: Mark D Powell on 5 Dec 2009 10:26 On Dec 4, 11:48 am, bugbear <bugbear(a)trim_papermule.co.uk_trim> wrote: > 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 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. If the data is not skewed then the presence or absence of histograms will probably not have a major mpact on the plan. My question to you though is major/minor status of what? Will you be seeking all occurrences of whatever with a specific major status or all rows with a specific minor status? What I am trying to bring into consideration is if the status columns need to be associated with another key. That is you might need the index to be major/minor/ part_no or something like this depending on it major/minor is the PK of one of your tables or jsut really an attribute set of some of your tables. The three part key would allow more selective determination of the target rows before the table has to be accessed. HTH -- Mark D Powell --
From: Mladen Gogala on 5 Dec 2009 16:35 On Sat, 05 Dec 2009 14:18:38 +0100, Robert Klemme wrote: > Are there cases where it makes sense to use a FBI to get "multi column" > histogram information? Hmmm, let me quote from the 11g book: if the columns are related in the way that breaks the 3rd normal form, such statistics would make sense. The famous example by Jonatan was about zodiac signs and months of birth. The 10g CBO would produce an incorrect estimate for the number of people with the sign of Capricorn born in June. There are, of course, none, but you can't know that without a multi-column histogram. -- http://mgogala.byethost5.com
From: Robert Klemme on 6 Dec 2009 07:33 On 05.12.2009 22:35, Mladen Gogala wrote: > On Sat, 05 Dec 2009 14:18:38 +0100, Robert Klemme wrote: > >> Are there cases where it makes sense to use a FBI to get "multi column" >> histogram information? > > Hmmm, let me quote from the 11g book: if the columns are related in the > way that breaks the 3rd normal form, such statistics would make sense. > The famous example by Jonatan was about zodiac signs and months of birth. > The 10g CBO would produce an incorrect estimate for the number of people > with the sign of Capricorn born in June. There are, of course, none, but > you can't know that without a multi-column histogram. That makes a lot of sense! Thank you, Mladen! Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
From: bugbear on 7 Dec 2009 09:54 Robert Klemme wrote: > Are there cases where it makes sense to use a FBI to get "multi column" > histogram information? Of course, queries then would also have to use > that concatenated value as query criteria which makes usage of this > quite nasty (especially if the SQL is generated by some kind of > persistence container). I though about this rather carefully. It is possible to do this (in my case, at least) However, the "accurate" statistics associated with the FBI make it quite important that there are not "too many" other indexes that the CBO might consider. Because due to the inapropriate estimation of selectivity the CBO is quite determined to use almost-anything-other that YOUR FBI. It doesn't "know" that the apparently low selectivity of your FBI is because it's the only index with accurate stats! BugBear
From: Jonathan Lewis on 11 Dec 2009 10:43
"bugbear" <bugbear(a)trim_papermule.co.uk_trim> wrote in message news:i7ednZdyeur2oITWnZ2dnUVZ7txi4p2d(a)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.com |