SQL Tuning

# Thread: SQL Tuning

1. Member
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. Junior Member
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. Member
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

Join Date
Sep 2002
Location
England
Posts
7,334
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. Foreign Script Kiddie
Join Date
Aug 2002
Location
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.

6. Member
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. Foreign Script Kiddie
Join Date
Aug 2002
Location
Posts
5,253
It's hevily dependent on the index clustering factor: http://dizwell.com/main/content/view/92/136/

8. Moderator
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