| SQL Tuning [message #169363] |
Wed, 26 April 2006 07:42  |
gajini
Messages: 262
Registered: January 2006
|
Senior Member |
|
|
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
|
|
|
|
| Re: SQL Tuning [message #169458 is a reply to message #169363] |
Wed, 26 April 2006 22:25  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Selectivity refers to the proportion of the table represented by a column or predicate. For example: if STATUS = 'CLOSED' returns 70% of the table we would say it has POOR selectivity. When selectivity exceeds 10%, it is frequently more efficient to perform a full table scan rather than use an index.
Cardinality refers to the number of rows matching a key or predicate. Eg. If the INVOICE_LINE table contains between 1 and 10 rows for each different INVOICE_NO, the cardinality is between 1 and 10. Depending on the application, this may be good or bad.
If you are joining two tables on a key with cardinality >1 on both tables, then you can get a cartesian-like join. eg. Join tables A and B on a key that has cardinality of 4 in table A and 6 on table B, you will get 6x4 = 24 rows per join key.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
