Re: density calculation when histograms are involved
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: exriscer@xxxxxxxxx
- Date: Thu, 13 Sep 2007 23:02:36 +0200
On 9/13/07, LS Cheng <exriscer@xxxxxxxxx> 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"
--
//www.freelists.org/webpage/oracle-l
Other related posts: