Database Reference
In-Depth Information
The two
SELECT
statements appear to be the same, except for the predicate values (20 in the first statement and 30
in the second). Although the two
SELECT
statements have the same form, the optimizer treats them differently because
of the
CHECK
constraint on the
Tl.C2
column, as shown in the execution plan in Figure
26-3
.
Figure 26-3.
Execution plans with predicate values within and outside the CHECK constraint boundaries
From the execution plan, you can see that, for the first query (with
T1.C2
=
20
), the optimizer accesses the data
from both tables. For the second query (with
Tl.C2
=
30
), the optimizer understands from the corresponding
CHECK
constraint on the column
Tl.C2
that the column can't contain any value outside the range of
10
to
20
. Thus, the
optimizer doesn't even access the data from the tables. Consequently, the relative estimated cost of the second query
is 0 percent.
I explained the performance advantage of referential integrity in detail in the “Declarative Referential Integrity”
section of Chapter 18.
Therefore, you should use domain and referential constraints not only to implement data integrity but also to
facilitate the optimizer in generating efficient query plans. To understand other performance benefits of domain and
referential integrity, please refer to the “Using Domain and Referential Integrity” section of Chapter 18.
Adopting Index-Design Best Practices
The most common optimization recommendation—and frequently one of the biggest contributors to good
performance—is to implement the correct indexes for the database workload. Indexes are unlike tables, which are
used to store data and can be designed even without knowing the queries thoroughly (as long as the tables properly
represent the business entities). Instead, indexes must be designed by reviewing the database queries thoroughly.
Except in common and obvious cases, such as primary keys and unique indexes, please don't fall into the trap of
designing indexes without knowing the queries. Even for primary keys and unique indexes, I advise you to validate the
applicability of those indexes as you start designing the database queries. Considering the importance of indexes for
database performance, you must be careful when designing indexes.
