Re: density calculation when histograms are involved

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Thu, 13 Sep 2007 23:02:36 +0200
Message-ID: <4ef2fbf50709131402w4b201e9dg8b201ee6f55c035@mail.gmail.com>

On 9/13/07, LS Cheng <exriscer_at_gmail.com> wrote:  > Does any one know in Oracle 9.2 when histograms are involved how is density
> calculated? Without histograms is 1/NDV but I cant find a suitable way to
> find the density when there are histograms.
>
> This question is because I have a 10053 trace file with this:
>
> SINGLE TABLE ACCESS PATH
> Column: COD_C Col#: 2 Table: EMP_CL Alias: CL
> NDV: 3 NULLS: 0 DENS: 4.0526e-09
> FREQUENCY HISTOGRAM: #BKT: 37012907 #VAL: 3
> Column: COD_A Col#: 3 Table: EMP_CL Alias: CL
> NDV: 2 NULLS: 0 DENS: 4.0526e-09
> FREQUENCY HISTOGRAM: #BKT: 37012907 #VAL: 2
> TABLE: EMP_CL ORIG CDN: 123376357 ROUNDED CDN: 442543 CMPTD CDN:
> 442543
> Access path: tsc Resc: 35875 Resp: 35875
> BEST_CST: 35875.00 PATH: 2 Degree: 1
>
> The query should returns around 3 million rows but I dont understand how the
> computed cardinality is 442543? I suspect it calculates that using density
> which shows 4.0526e-09

In the simple case of Frequency Histograms, density = 0.5 / num_rows

0.5 / 123376357 = 4.0526E-09

What is the SQL statement ? If it's "where column = constant", and constant matches one of the values in the histogram, it is simply the count of the rows having that value - i.e. the difference between endpoint_number in xxx_histograms
and the previous endpoint_number, for example

ENDPOINT_VALUE ENDPOINT_NUMBER

42                         100
78                         300
99                         700
if constant = 42 => card = 100

if constant = 78 => card = 300-100 = 200 if constant = 99 => card = 700-300 = 400

If it's not a value contained in the histogram, it is num_rows*density=0.5 rounded up to 1 - which is not your case for sure.

At least until 10g, I don't know in 11g (but probably the same).

HTH
Alberto

-- 
Alberto Dell'Era
"the more you know, the faster you go"
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 13 2007 - 16:02:36 CDT