Oracle FAQ Your Portal to the Oracle Knowledge Grid
 HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US

Home -> Community -> Mailing Lists -> Oracle-L -> RE: More on histograms, density and skew (Histograms: Myths and Facts)

# RE: More on histograms, density and skew (Histograms: Myths and Facts)

From: Schultz, Charles <sac_at_uillinois.edu>
Date: Mon, 31 Jul 2006 11:12:40 -0500

I am wondering if perhaps the "SKEW ONLY" option of method_opt is misnamed - you still want histograms even if all the popular values are centered around the median. Would it not better to put the most popular values in histograms (irregardless of the distribution)?

> _____________________________________________
> From: Schultz, Charles
> Sent: Monday, July 31, 2006 10:33 AM
> To: oracle-l_at_freelists.org
> Subject: More on histograms, density and skew (Histograms: Myths and Facts)
>
> Sorry to beat this into the ground, but the more I read, the more questions I have.
>
> Wolfgang's paper mentions Oracle's calculation of density (page 6 of "Histograms: Myths and Facts"):
> *> Without a histogram density = 1/NDV?
> *> With a height-balanced histogram density = > Ó cnt2 / ( num_rows> ~ * > Ó cnt )å
> *> With a frequency histogram density =1/( 2 * num_rows~ )
>
> I am not exactly sure how close density is supposed to be to the mathematical (or rather, statistical) definition of skew - my biggest problem with statistical skew is that a set with symmetric distribution is not considered skewed at all, but in terms of Oracle histograms, the set could have one or more popular values which would benefit from a histogram bucket.
>
> Also, I was reading Jonathan Lewis's book ("Cost-Based Oracle Fundamentals"), chapter 7 - wow, using SKEW as a column name for an arbitrary table sure makes my research more confusing! *grin*
>
> So how does Oracle define skew? If the method_opt for size SKEW ONLY is not working 100% (Wolfgang mentions for 10.2.0.1, have not verified with 10.2.0.2 yet), does anyone know exactly why? I am very curious what this procedure is doing.
>
> Just to be clear, I am trying to learn more about what exactly "skew" is, as Oracle defines it. In an ideal world, Oracle would not be limited to 254 histogram buckets, and the statistics would be able to describe all the data precisely. As a DBA, I am trying to fill in the gaps between Utopia and reality.
>
> charles schultz
> oracle dba
```--