Databases Reference
In-Depth Information
In order to get the intersection of these sets, we need to multiply the selectivity values of
both predicate clauses, 0.03864256 * 0.56071284 to get 0.0216673795624704. Finally, the
calculated selectivity is multiplied by the number of records to give the estimated number
of records as 0.0216673795624704 * 121,317, or 2,628.62, which is the value shown in the
graphical plan in Figure 3-7.
Figure 3-7:
Cardinality estimation example using an
AND
'ed predicate.
It is also worth noticing that if these two columns,
ProductID
and
OrderQty
, were
correlated (which is not the case in this example), then this method to estimate the
cardinality would be incorrect. Two methods to help with correlated columns are
using multi-column statistics, as mentioned before, and filtered statistics, which will be
explained later in this chapter.
Finally, let's test the same query with an
OR
'ed predicate to see how the information
revealed by the histogram will be helpful.
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
ProductID
=
870
OR
OrderQty
=
1
Listing 3-19.
By definition, an
OR
'ed predicate is the union of the sets of rows of both clauses, without
duplicates. That is, this should be the rows estimated for
ProductID = 870
plus the
rows estimated for
OrderQty = 1
, but if there are any rows that may belong to both
