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 Received on Fri Jul 20 2007 - 09:51:49 CDT