SQL Tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: SQL Tuning

  1. #1
    Join Date
    Feb 2006
    Posts
    162

    SQL Tuning

    Hi,

    Can anyone explain me,what is meant by selectivity and cardinality estimates in SQL tuning? How it's helpful for SQL Tuning?

    Thanks,
    Malru

  2. #2
    Join Date
    Apr 2006
    Posts
    50
    I'm quoting from Jonathan Lewis's book Cost-based Oracle Fundamentals which explains all and I would recommend to everyone, but here's a summary

    '... the predicted number of rows(cardinality) generated by an operation plays a crucial part in selecting initial join orders and optimum choice of indexes...'

    '...the optimizer's calculation of cardinality are based on estimating the expected fraction of the rows in the current data set that would pass a particular test. That fraction is the number we call the selectivity. After you've worked ou the selectivity, the cardinality is simply selectivity * (number of input rows).'

  3. #3
    Join Date
    Feb 2006
    Posts
    162
    Thanks Graham,

    Can you explain me with a simple example?Please help me in clearing this doubt.

    Thanks,
    Malru

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    which bit of the explanation doesnt make sense, thats as clear as it can be really.

    Cardinality is the number or rows expected to be returned, selectivity is the number of rows in the current data set that would pass the selected test

    multiple selectivity by cardinality to get the rows

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If a table has 1,000 rows and the optimizer expects that 10 of those rows will be selected as part of a query then "10" is the expected cardinality of the query and the selectivity is 10/1000 = 0.01.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Feb 2006
    Posts
    162
    Thanks Slimdave,

    Now i got clear idea about Selectivity & Cardinality.I've read some where,both these values helps in determining what percentage of rows that query will retrieve,based on this,the optimizer will decide whether to go for Index scan or Full table scan.Can you explain this scenario with a example?

    Thanks
    Malru

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It's hevily dependent on the index clustering factor: http://dizwell.com/main/content/view/92/136/
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    http://www.beaconinfotechcorp.com/art_001.html may help to understand how CF is calculated.

    Tamil
    www.beaconinfotechcorp.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width