Databases Reference
In-Depth Information
3
Query Optimization
and Plan Selection
It is a capital mistake to theorize before one has the data. Insensibly one begins
to twist facts to suit theories, instead of theories to suit facts.
—Sir Arthur Conan Doyle (1859-1930)
f you ever wanted to learn to play the piano, there are many different approaches that
people have tried over the past three centuries. The traditional recommended way is
to sign up with a professional teacher and take lessons one-on-one. Others have tried a
variety of methods of self-instruction, such as using topics like
Piano for Dummies
,
watching videos, or just getting some sheet music and start playing. Regardless of which
method you choose, there is another variable, how to proceed to learn the piece you
want to play: hands separate and then together, hands together from the beginning,
learning in sections, etc. Therefore, to get to the final goal, there are literally dozens and
possibly hundreds of paths one can take to get the final “correct” result. The quality of
the result may vary, but you can get there many different ways. For database queries
there is also a multipath possibility to get the same correct result, and it is important to
be able to analyze the different paths for the quality of the result, in other words, the
performance of the system to get you the correct result and choose the best path to get
you there.
This chapter focuses on the basic concepts of query optimization needed to under-
stand the interactions between physical database design and query processing. We start
with a simple example of query optimization and illustrate how the input/output (I/O)
time estimation techniques in Chapter 2 can be applied to determining which query
I
31
