Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: density calculation when histograms are involved
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 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-lReceived on Thu Sep 13 2007 - 16:02:36 CDT