RE: Hybrid histograms in 11g ?

Hello Mark,
Excellent explanation of hybrid/rectangular/mulsidimentional histograms ! I already knew what is all about ..;)
My question was whether someone already has played/evaluated with this new feature. Chris Antognini is till now the one from the list, who evaluated this. Anyway, I will also take time to play with this. As for me, I think that introducing Multidimentional histograms a big step into the right direction(supporting CBO to take the right decisions).

Best Regards. Milen

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark Sent: Friday, July 20, 2007 4:52 PM
To: makulev_at_gmx.net; oracle-l_at_freelists.org Subject: RE: Hybrid histograms in 11g ?

I believe this is the ability of 11g to create histograms on multiple columns. This helps Oracle deal with columns that are correlated. If you have a column "BIRTH_MONTH" and a column "ZODIAC_SIGN", you can say that 1/12 of the population will have a BIRTH_MONTH of DECEMBER, and 1/12 of the population will have a ZODIAC_SIGN of TAURUS. If you ask the optimizer to estimate how many people have a BIRTH_MONTH of DECEMBER and a ZODIAC_SIGN of TAURUS, it will estimate 1/144. But, we know the answer is 0 (since TAURUS is only people born between Apr. 20th - May 20th). With histograms on multiple columns, the optimizer will now be able to understand these types of correlations and handle them correctly. This will allow for much better cardinality estimates.

Hope that helps,

-Mark

PS I'm not an 11g Beta tester. The above is based purely on what I've read and heard about 11g, and could be completely wrong.

--

Mark J. Bobak
Senior Database Administrator, System & Product Technologies ProQuest 789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346 734.997.4059  or 800.521.0600 x 4059 mark.bobak_at_il.proquest.com www.proquest.com www.csa.com

ProQuest...Start here.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Milen Kulev Sent: Friday, July 20, 2007 5:19 AM
To: oracle-l_at_freelists.org
Subject: Hybrid histograms in 11g ?

Hi listers,
I have just read the Oracle White paper: http://www.oracle.com/technology/products/database/oracle11g/pdf/performance-11g-whitepaper.pdf

On page 11 there is a following statement: "
...
ding better information to the CBO by correlating statistics, such as Number of Distinct Values (NDV) and histograms, on multiple column ... "

Are these so called "Hybrid histograms" ? If yes, is there still a constraint of 254 buckets per histogram? Any ideas, experiences from "early birds" in this list ?

Best Regrads.
Milen

--

Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Sun Jul 22 2007 - 08:57:20 CDT