Grokbase › Groups › Oracle › oracle-l › September 2007
FAQ
Hi

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
!

Thanks

Search Discussions

  • Alvaro Jose Fernandez at Sep 13, 2007 at 5:55 pm ⇧
    Hi Cheng,



    Recommend you to read:



    1st: Wolfgang's
    http://www.centrexcc.com/Histograms%20-%20Myths%20and%20Facts.pdf



    2nd: Alberto Dell'Era "Join over Histograms" paper :
    http://www.adellera.it/investigations/join_over_histograms/JoinOverHisto
    grams.pdf (covering much more complex cases)



    regards.
  • LS Cheng at Sep 13, 2007 at 6:26 pm ⇧
    I am reading right now Wolfgangs paper, didnt know Alberto's though

    Thanks

    --
    LSC
    On 9/13/07, Alvaro Jose Fernandez wrote:

    Hi Cheng,



    Recommend you to read:



    1st: Wolfgang's
    http://www.centrexcc.com/Histograms%20-%20Myths%20and%20Facts.pdf



    2nd: Alberto Dell'Era "Join over Histograms" paper :
    http://www.adellera.it/investigations/join_over_histograms/JoinOverHistograms.pdf
    (covering much more complex cases)



    regards.
    --
    http://www.freelists.org/webpage/oracle-l
  • Rich Jesse at Sep 13, 2007 at 6:40 pm ⇧
    Might as well complete the list with Jonathan's Cost-Based Oracle Fundamentals:

    http://www.apress.com/book/view/1590596366

    At a $25 steal for the eBook, it's been open on my desktop for two solid
    months now (I'm only on chapter 7!).

    Rich
    Hi Cheng,



    Recommend you to read:



    1st: Wolfgang's
    http://www.centrexcc.com/Histograms%20-%20Myths%20and%20Facts.pdf



    2nd: Alberto Dell'Era "Join over Histograms" paper :
    http://www.adellera.it/investigations/join_over_histograms/JoinOverHisto
    grams.pdf (covering much more complex cases)



    regards.
    --
    http://www.freelists.org/webpage/oracle-l
  • LS Cheng at Sep 13, 2007 at 9:03 pm ⇧
    Hi

    I've got the book but I am a bit confused in the part where it talks about
    selectivity and histograms in page 43 which says

    10g uses num_distinct to work out the selectivity which is cardinality =
    num_rows / num_distinct if there is histogram then cardinality = num_rows *
    density

    but I am in 9i so.... I am also reading chapter 7 btw

    Thanks

    --
    LSC
    On 9/13/07, Rich Jesse wrote:

    Might as well complete the list with Jonathan's Cost-Based Oracle
    Fundamentals:

    http://www.apress.com/book/view/1590596366

    At a $25 steal for the eBook, it's been open on my desktop for two solid
    months now (I'm only on chapter 7!).

    Rich
    Hi Cheng,



    Recommend you to read:



    1st: Wolfgang's
    http://www.centrexcc.com/Histograms%20-%20Myths%20and%20Facts.pdf



    2nd: Alberto Dell'Era "Join over Histograms" paper :
    http://www.adellera.it/investigations/join_over_histograms/JoinOverHisto
    grams.pdf (covering much more complex cases)



    regards.

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Baumgartel, Paul at Sep 13, 2007 at 6:52 pm ⇧
    Also consult Jonathan Lewis's book Cost-Based Oracle.


    Paul Baumgartel
    CREDIT SUISSE

    Information Technology
    Securities Processing Databases Americas
    One Madison Avenue
    New York, NY 10010
    USA

    Phone 212.538.1143
    paul.baumgartel_at_credit-suisse.com
    www.credit-suisse.com



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Alvaro Jose
    Fernandez
    Sent: Thursday, September 13, 2007 1:56 PM
    To: exriscer_at_gmail.com; oracle-l
    Subject: RE: density calculation when histograms are involved

    Hi Cheng,



    Recommend you to read:



    1st: Wolfgang's
    http://www.centrexcc.com/Histograms%20-%20Myths%20and%20Facts.pdf



    2nd: Alberto Dell'Era "Join over Histograms" paper :
    http://www.adellera.it/investigations/join_over_histograms/JoinOverHisto
    grams.pdf (covering much more complex cases)



    regards.

    Please access the attached hyperlink for an important electronic communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
  • Alberto Dell'Era at Sep 13, 2007 at 9:02 pm ⇧

    On 9/13/07, LS Cheng 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 13, '07 at 5:28p
activeSep 13, '07 at 9:03p
posts7
users5
websiteoracle.com

5 users in discussion

LS Cheng: 3 posts Rich Jesse: 1 post Baumgartel, Paul: 1 post Alberto Dell'Era: 1 post Alvaro Jose Fernandez: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase