Database Reference
In-Depth Information
Chapter 2
Key Concepts
The aim of this chapter is threefold. First, to avoid unnecessary confusion, I introduce some terms that are used
repeatedly throughout this topic. The most important include
selectivity
and
cardinality
,
cursor
,
soft
and
hard parses
,
bind variable peeking
, and
adaptive cursor sharing
. Second, I describe the life cycle of SQL statements. In other
words, I describe the operations carried out in order to execute SQL statements. During this discussion, special
attention is given to parsing. And third, I describe how to instrument application code and database calls.
Selectivity and Cardinality
The
selectivity
is a value between 0 and 1 representing the fraction of rows filtered by an operation. For example,
if an access operation reads 120 rows from a table and, after applying a filter, returns 18 of them, the selectivity is
0.15 (18/120). The selectivity can also be expressed as a percentage, so 0.15 can also be expressed as 15 percent.
When the selectivity is close to 0, it's said to be
strong
. When it's close to 1, it's said to be
weak
.
■
I used to use the terms
low/high
or
good/bad
instead of
strong/weak
. I stopped using
low/high
because they
don't make it clear whether they refer to the degree of selectivity or to its numerical value. In fact, various and conflicting
definitions exist. I stopped using
good/bad
because it isn't sensible to associate a positive or negative quality to selectivity.
Caution
The number of rows returned by an operation is the
cardinality
. Formula 2-1 shows the relationship between
selectivity and cardinality. In this formula, the
num_rows
value is the number of input rows.
cardinality=selectivitynum_rows
×
Formula 2-1.
Relationship between selectivity and cardinality
■
In the relational model, the term
cardinality
refers to the number of tuples in a relation. Because a relation
never contains duplicates, when the relation is unary, the number of tuples corresponds to the number of distinct values
it represents. Probably for this reason, in some publications, the term
cardinality
refers to the number of distinct values
stored in a particular column. Because SQL allows tables containing duplicates (that is, SQL doesn't adhere to the
relational model in this regard), I never use the term
cardinality
to refer to the number of distinct values in a column.
In addition, Oracle itself isn't consistent in the definition of the term. Sometimes, in the documentation, Oracle uses it for
the number of distinct values, and sometimes for the number of rows returned by an operation.
Caution
