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

Structure of This Book

For your own purposes, you might not need to read this book in order,
cover to cover. The following summary should help you work out which
parts of the book you can skip or skim, which parts you can reserve
for occasional reference, and which parts you should read thoroughly,
in which order:

Chapter 1

Provides an overview of the motivation for SQL tuning and the
approach this book takes to the problem, as well as some side
benefits that come with following that approach. This chapter is
short and easy, and I recommend you read it first.

Chapter 2

Describes how databases access individual tables with full table
scans and indexed reads, how databases join tables, and the tradeoffs
between these alternatives. If you already know the basics of how
databases execute queries, you might be able to skip or skim this
chapter.

Chapter 3

Covers how to read and interpret an execution plan on Oracle,
Microsoft SQL Server, and DB2. If you have done any SQL tuning at
all, you likely already know this for whatever database concerns you.
Chapter 3 also separates the coverage of each
database and even repeats material that applies to more than one
database, so you need to read only the section or sections that
matter to you.

Chapter 4

Covers how to control execution plans on Oracle, Microsoft SQL
Server, and DB2. This discussion includes some generic techniques
that provide some control of execution plans on any relational
database. If you have done significant SQL tuning, you might already
know how to control execution plans for whatever database concerns
you. Like Chapter 3, Chapter 4
also separates the coverage of each database and even repeats
material that applies to more than one database, so you need to read
only the section or sections that matter to you.

Chapter 5

Covers the foundation for the rest of the book, which
won't even make sense unless you read this chapter
first. This chapter introduces a shorthand, pictorial language that
greatly clarifies the core of a SQL tuning problem. Chapter 5 lays a foundation that makes the rest of the
book far clearer and more concise than it could be without this
pictorial language. Read the chapter and learn the pictorial language
well before you read the rest of the book. (The language takes some
patience to learn, but it is worth it!)

Chapter 6

Explains how to use the query diagrams you learned to make in Chapter 5 to tune 2-way, 5-way, even 115-way joins
rapidly, without trial and error. This is the big payoff, so
don't stop before you understand this material.

Chapter 7

Shows you how to tune complex queries such as queries containing
subqueries that do not fit the standard, simple,
n-way-join template. As a bonus, this chapter
will also describe how to diagnose and repair logic problems with the
SQL (as opposed to performance problems) that become obvious once you
know how to build and interpret the query diagrams.

Chapter 8

Justifies the rules of thumb I discuss in Chapter 5-Chapter 7. If you
don't quite trust me, or if you just think you would
apply this book's method better if you had an
understanding of why the method works, this chapter should help. You
might even want to read this chapter early if you are losing patience
with rote application of mysterious rules earlier in the book.

Chapter 9

Covers advanced material you might refer to only as problems arise,
if you would rather not read so much cover-to-cover. However, I
recommend at least skimming this chapter to learn what is there, so
you can recognize the problems when you see them.

Chapter 10

Explains how to cope with even
"impossible" problems, problems
with no fast execution plan capable of delivering the rows required
by the original SQL. This material is highly recommended, but I leave
it for last because until you know how to get the best execution
plan, you cannot recognize which (surprisingly few) problems require
these outside-the-box solutions.

Appendix A

Provides solutions to the exercises at the ends of Chapter 5, Chapter 6, and Chapter 7.

Appendix B

Follows the solution of a SQL tuning problem through the whole
diagramming method, from start to finish, on Oracle, DB2, and SQL
Server. If you like to work from complete, end-to-end examples, this
appendix is for you.

Glossary

Defines key terms and phrases used in the book.