SQL Tuning [Electronic resources] نسخه متنی

Chapter 2. Data-Access Basics

Come, fill the Cup, and in the fire of Spring

The Winter garment of Repentance fling:

The bird of Time has but a little way

To flyand Lo! the Bird is on the Wing.

Omar Khayyam, translated by Edward Fitzgerald The Rubaiyat

You need a clear understanding of the operations of arithmetic to
solve an algebra problem. Similarly, you must understand how a
database reaches data in individual tables and how it joins data from
multiple tables before you can understand how to combine these
operations for an optimized execution plan. This book focuses on
access methods that are most important to real-world queries and
points out which methods are rarely or never useful.

You may find this chapter to be
deceptively named; some of these data-access
"basics" are quite advanced and
obscure, because even the most basic of database operations can be
quite involved at the detail level. I urge you not to get
discouraged, though. While I include lots of gory detail for those
few who really want it and for the relatively rare cases for which it
is useful, you can tune quite well with just a passing understanding
of indexed access and nested-loops joins. Optimizing a query to make
it run faster requires only a high-level understanding of the
material in this chapter.

I present this chapter in all its gory detail, though, for two
reasons:

Some readers will find the later material much easier to follow and
remember if they have a concrete, detailed picture in mind when I
refer to specific methods of table access and table joins in later
chapters. For example, such readers would have a hard time following
and remembering rules of thumb about when to prefer hash joins over
nested-loops joins if they knew these join methods only as black-box
processes. If you are such a concrete thinker (like myself), this
chapter, in all its detail, will help you understand the rest of the
book.

The same people who tune queries are often asked awkward questions,
like "Why does this query take 12 times longer to
return 200 rows than this other query takes to return 1,000
rows?" Another common question is
"Shouldn't we be using
<InsertThisYear'sFashionableObjectType>
to speed up this query?" Only with a detailed
understanding of the basics discussed in this chapter is it possible
to answer such questions well.

A word of explanation up front: many of the specifics in this chapter
come from the behavior of Oracle. I find that highly specific
descriptions help intuition in performance and tuning, because you
can hold a detailed, concrete picture in your head. I could have
chosen another database to describe table layouts and table-access
and join methods, but no single choice would please everyone. I have
found that, for the most part, the differences between database
brands really do not matter to SQL tuning. In
the few cases in which a vendor-specific implementation matters, I do
describe the differences in detail.