Database Reference
In-Depth Information
■
I tried to keep the following examples as simple as possible. as a result, at first glance, some query
transformations may seem useful only when the query optimizer has to process SQL statements of bad quality. By
bad
quality
I mean SQL statements that include, for example, redundant or conflicting operations. But you have to consider that
the query optimizer will have to process SQL statements that are much more complex than in my examples. Just think about
the case of a query referencing several views that in turn reference other views, or a query generated by general-purpose
and ad-hoc query tools. When the query optimizer puts everything together, it's not uncommon that, for example, redundant
or conflicting operations appear. In addition, it's a good thing that the query optimizer recognizes odd situations and avoids
performing unnecessary processing. In addition, some query transformations allow you to write SQL statements in the most
natural, readable way, without exchanging clarity for performance. In fact, some query transformations are (quite) common
SQL optimization techniques that, when applied manually, produce less readable SQL statements.
Note
Count Transformation
The purpose of
count transformation
is to transform
count(column)
expressions to
count(*)
. This query
transformation was introduced because a
count(*)
can be processed using a larger selection of indexes than a
count(column)
. Chapter 13 talks more about this. Count transformation is a heuristic-based query transformation
that can be applied when the column referenced in the
count
function has an associated
NOT NULL
constraint
(check constraints can't be used for this purpose, though). Note that count transformation also transforms
count(1)
expressions to
count(*)
.
The following example, based on the
count_transformation.sql
script, illustrates this query transformation.
Notice that the orginal query contains the
count(n2)
expression:
SELECT count(n2)
FROM t
If a
NOT NULL
constraint is defined on the
n2
column, count transformation translates the
count(n2)
in
count(*)
and produces the following query:
SELECT count(*)
FROM t
Common Sub-Expression Elimination
The purpose of
common sub-expression elimination
is to remove duplicate predicates and thereby avoid processing
the same operation several times. This is a heuristic-based query transformation.
In the following example, based on the
common_subexpr_elimination.sql
script, notice how the two disjunctive
predicates are overlapping. In fact, all rows that fulfill the first one necessarly also fulfill the second one:
SELECT *
FROM t
WHERE (n1 = 1 AND n2 = 2) OR (n1 = 1)
Common sub-expression elimination removes the redundant predicate and produces the following query:
