SQL Tuning

[malru]

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

[graham_o]

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).'

[malru]

Thanks Graham,

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

Thanks,
Malru

[davey23uk]

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

[slimdave]

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.

[malru]

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

[slimdave]

It's hevily dependent on the index clustering factor: http://dizwell.com/main/content/view/92/136/

[tamilselvan]

http://www.beaconinfotechcorp.com/art_001.html may help to understand how CF is calculated.

Tamil
www.beaconinfotechcorp.com