1. Chapter 1 Introduction

   1. Why Tune SQL?

   2. Who Should Tune SQL?

   3. How This Book Can Help

   4. A Bonus

   5. Outside-the-Box Solutions

2. Chapter 2 Data-Access Basics

   1. Caching in the Database

   2. Tables

   3. Indexes

   4. Uncommon Database Objects

   5. Single-Table Access Paths

   6. Calculating Selectivity

   7. Joins

3. Chapter 3 Viewing and Interpreting Execution Plans

   1. Reading Oracle Execution Plans

   2. Reading DB2 Execution Plans

   3. Reading SQL Server Execution Plans

4. Chapter 4 Controlling Execution Plans

   1. Universal Techniques for Controlling Plans

   2. Controlling Plans on Oracle

   3. Controlling Plans on DB2

   4. Controlling Plans on SQL Server

5. Chapter 5 Diagramming Simple SQL Queries

   1. Why a New Method?

   2. Full Query Diagrams

   3. Interpreting Query Diagrams

   4. Simplified Query Diagrams

   5. Exercises (See Section A.1 for the solution to each exercise.)

6. Chapter 6 Deducing the Best Execution Plan

   1. Robust Execution Plans

   2. Standard Heuristic Join Order

   3. Simple Examples

   4. A Special Case

   5. A Complex Example

   6. Special Rules for Special Cases

   7. Exercise (See Section A.2 for the solution to the exercise.)

7. Chapter 7 Diagramming and Tuning Complex SQL Queries

   1. Abnormal Join Diagrams

   2. Queries with Subqueries

   3. Queries with Views

   4. Queries with Set Operations

   5. Exercise (See Section A.3 for the solution to the exercise.)

8. Chapter 8 Why the Diagramming Method Works

   1. The Case for Nested Loops

   2. Choosing the Driving Table

   3. Choosing the Next Table to Join

   4. Summary

9. Chapter 9 Special Cases

   1. Outer Joins

   2. Merged Join and Filter Indexes

   3. Missing Indexes

   4. Unfiltered Joins

   5. Unsolvable Problems

10. Chapter 10 Outside-the-Box Solutions to Seemingly Unsolvable Problems

    1. When Very Fast Is Not Fast Enough

    2. Queries that Return Data from Too Many Rows

    3. Tuned Queries that Return Few Rows, Slowly

1. Appendix A Exercise Solutions

   1. Chapter 5 Exercise Solutions

   2. Chapter 6 Exercise Solution

   3. Chapter 7 Exercise Solution

2. Appendix B The Full Process, End to End

   1. Reducing the Query to a Query Diagram

   2. Solving the Query Diagram

   3. Checking the Execution Plans

   4. Altering the Database to Enable the Best Plan

   5. Altering the SQL to Enable the Best Plan

   6. Altering the Application

   7. Putting the Example in Perspective

3. Glossary

4. Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. The animal on the cover of SQL Tuning is a salamander. Though mature salamanders bear a superficial resemblance to small lizards, salamanders are not reptiles; rather, they are amphibians that retain their tails as adults. Like all amphibians, a salamander begins life underwater as a gelatinous egg and develops through a series of stages. Newly hatched salamander larvae resemble tadpoles (the larval form of toads and frogs) and breathe through gills. As they mature, salamanders develop legs and lungs, which allow them to leave the water and breathe air. But they remain in or around streams, rivers, ponds, lakes, or moist woodlands throughout their lives. They must return to a freshwater source to lay their eggs.

The most immediately recognizable difference between adult salamanders and lizards is the former's lack of scales; a salamander's skin is smooth and porous and is used to absorb moisture. Salamanders' skin can be any of a variety of colors--from brown or black to yellow or red-- and is often covered with dark spots, bars, or stripes. As they grow, salamanders molt their skin, usually every few days or every few weeks. Salamanders also have the ability to shed and regrow their tails and other parts of their body that become severed or damaged. Unlike other amphibians, salamanders are carnivorous at every stage of their life cycle (tadpoles are herbivorous), and their diet consists of worms, insects, snails, and small fish.

Mature salamanders are usually about 4 to 8 inches long, though they can be as short as 2 inches and as long as 70 inches. Most have four legs, though some have only two forelegs. Their front feet each have four clawless toes, while hind feet, when present, have five toes. Salamanders are nocturnal and usually divide their time between the land and water, though some live exclusively in the water and a few are purely landdwelling. When they swim, they make little use of their limbs. Instead, they use their laterally compressed (i.e., taller than it is wide) tail and muscle contraction to propel themselves through the water, as eels do. Some tree-dwelling salamanders have prehensile tails, which they can use to grasp branches.

The name salamander (from the Greek salamandra) originally applied to a legendary creature that could live in and extinguish fire. Aristotle is largely responsible for perpetuating this myth; in his History of Animals, he supports the story that the salamander "not only walks through the fire but puts it out in doing so." The application of the name salamander to an actual amphibian was first recorded in 1611, at which time the supernatural characteristics of the mythological animal became attributed to the actual animal. The common belief (mistaken, of course) that salamanders can endure fire persisted well into the 19th century. Brian Sawyer was the production editor and copyeditor for SQL Tuning. Matt Hutchinson was the proofreader. Darren Kelly and Claire Cloutier provided quality control. Angela Howard wrote the index.

Ellie Volckhausen designed the cover of this book, based on a series design by Edie Freedman. The cover image is a 19th-century engraving from the Dover Pictorial Archive. Emma Colby produced the cover layout with QuarkXPress 4.1 using Adobe's ITC Garamond font.

Melanie Wang designed the interior layout, based on a series design by David Futato. This book was converted by Julie Hawks to FrameMaker 5.5.6 with a format conversion tool created by Erik Ray, Jason McIntosh, Neil Walls, and Mike Sierra that uses Perl and XML technologies. The text font is Linotype Birka; the heading font is Adobe Myriad Condensed; and the code font is LucasFont's TheSans Mono Condensed. The illustrations that appear in the book were produced by Robert Romano and Jessamyn Read using Macromedia FreeHand 9and Adobe Photoshop 6. The tip and warning icons were drawn by Christopher Bing. This colophon was written by Brian Sawyer.