Databases Reference
In-Depth Information
Figure 3-6:
Cardinality estimation example using an inequality operator.
Let's now test a query with an
AND
'ed predicate (this example shows statistics as
estimated in SQL Server 2008; statistics for SQL Server 2008 R2 will have minimal
differences for a default sample).
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
ProductID
=
870
AND
OrderQty
=
1
Listing 3-18.
SQL Server will use the histograms of two distinct statistics objects here, one for each
predicate clause. We can use one histogram to obtain the estimated number of records for
ProductID = 870
, and the second histogram to obtain the estimated number of records
for OrderQty = 1
. Requesting
ProductID = 870 AND OrderQty = 1
will return
the intersection between both sets of records, so we should multiply the
selectivity
of both
clauses to obtain this value.
If you obtain the estimated number of rows for the predicate
ProductID = 870
alone, as explained before, you will get 4,688 rows. For a table with 121,317 records, this
corresponds to a selectivity of 4,688 / 121,317, or 0.03864256. In the same way, the
estimated number of rows for the predicate
OrderQty = 1
alone is 68,024 rows,
which corresponds to a selectivity of 68,024 / 121,317, or 0.56071284.
