Cardinality Estimator Series I: Learning Selectivity factor of Select Query and Predicate

A predicate is nothing but an expression that you write in your where clause that evaluates to either TRUE, FALSE or UNKNOWN.

Now there are two category of predicate

• Filter Predicate
• Join Predicate

Filter Predicate, the one which is used to search your result set generally the condition you write in ‘WHERE or HAVING’ clause.

Join Predicate, The equality and non-equality operator that are used while joining table in your DML statements are considered as join predicate.

Example

select e.emp_name,d.dept_name from dept d inner join emp e

on d.dept_id = e.dept_id  --Join Predicate

where d.dept_name = 'hr'  --Filter Predicate

Selectivity, It is a measure of how selective your predicate is? The formula to find the selectivity is

Selectivity = Rows qualifies a predicate / Total no of rows present in table.

Selectivity is always lays in the range of 0-1 ,

0.0     is considered as high selectivity

1.0     is considered as low selectivity

For example

select * from Sales.SalesOrderHeader

where SalesOrderID > 74000

so the selectivity of SalesIderId > 7400 will be

=Total record qualify SalesOrderId predicate / Total record present in SalesOrderHeader table

select count(*) as TotalRecord from sales.SalesOrderHeader

TotalRecord

-----------

31465

select count(*) as TotalQualifyingRows from Sales.SalesOrderHeader

where SalesOrderID > 74000

TotalQualifyingRows

-------------------

1123

So the Selectivity will be = 1123/31465.0

               

select 1123/31465.0 as Selectivity

Selectivity

---------------------------------------

0.0356904

Be in this world with your code; make sure your code speak loud!

Post Reference: Vikram Aristocratic Elfin Share