Home » RDBMS Server » Performance Tuning » SQL Tuning
SQL Tuning [message #169363] Wed, 26 April 2006 07:42 Go to next message
Messages: 262
Registered: January 2006
Senior Member

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

Re: SQL Tuning [message #169458 is a reply to message #169363] Wed, 26 April 2006 22:25 Go to previous message
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
Previous Topic: sizing sort area
Next Topic: Best practice for querying weighted sparse data
Goto Forum:

Current Time: Wed May 29 19:06:02 CDT 2024