RE: ** histograms

  • From: A Joshi <ajoshi977@xxxxxxxxx>
  • To: breitliw@xxxxxxxxxxxxx, "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
  • Date: Wed, 22 Nov 2006 12:41:37 -0800 (PST)

Thanks to Stephen Booth, Wolfgang Bretling , Ghassan Salem, Syed Jaffar 
Hussain, Alex Gorbachev,Alberto Dell'Era for the help. Detailed explanation and 
example were especially helpful. Thanks

  Below is  post from Alberto Dell'Era and Wolfgang :
  To add to the excellent explanation by Stephen  - imagine what happens
when the plan gets flushed from the library cache for some reason;
the new plan will depend on 'Y' or 'X' being the next value, so 
it will be "random" ... diagnosing this requires some time and an 
performance tuner, so many shops will simply think that "the 
is unstable" and move on (bad for reputation).

IMHO adding an histogram is as critical as (or even more critical than) 
say, an index; it mandates a lot of reasoning, planning and of course 

Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
  That is a very nice and accurate analysis. Allowing the CBO to make a more 
accurate cardinality estimates is what histograms are all about. That may have 
the consequence that the CBO chooses an index access path if a suitable index 
is available, but that is by no means the only possible consequence. Seeing 
histograms only in connection with an index is seeing it too narrowly.

PS. the testcase is in the paper 'Histograms - Myths and Facts" on my website.

At 11:41 AM 11/20/2006, Allen, Brandon wrote:
  I'm curious to see the example too.  My guess is that the histograms made a 
difference in the CBO's cardinality estimate, which in turn caused it to choose 
a different join method or join order.

Wolfgang Breitling
Centrex Consulting Corporation 
This email has been scanned by the MessageLabs Email Security System.
For more information please visit 

Sponsored Link

Mortgage rates near 39yr lows. $510,000 Mortgage for $1,698/mo -   Calculate 
new house payment

Other related posts: