Dedication;
Foreword;
Preface;
Objectives of This Book;
Audience for This Book;
Structure of This Book;
Conventions Used in This Book;
Comments and Questions;
Acknowledgments;
Chapter 1: Introduction;
1.1 Why Tune SQL?;
1.2 Who Should Tune SQL?;
1.3 How This Book Can Help;
1.4 A Bonus;
1.5 Outside-the-Box Solutions;
Chapter 2: Data-Access Basics;
2.1 Caching in the Database;
2.2 Tables;
2.3 Indexes;
2.4 Uncommon Database Objects;
2.5 Single-Table Access Paths;
2.6 Calculating Selectivity;
2.7 Joins;
Chapter 3: Viewing and Interpreting Execution Plans;
3.1 Reading Oracle Execution Plans;
3.2 Reading DB2 Execution Plans;
3.3 Reading SQL Server Execution Plans;
Chapter 4: Controlling Execution Plans;
4.1 Universal Techniques for Controlling Plans;
4.2 Controlling Plans on Oracle;
4.3 Controlling Plans on DB2;
4.4 Controlling Plans on SQL Server;
Chapter 5: Diagramming Simple SQL Queries;
5.1 Why a New Method?;
5.2 Full Query Diagrams;
5.3 Interpreting Query Diagrams;
5.4 Simplified Query Diagrams;
5.5 Exercises (See Section A.1 for the solution to each exercise.);
Chapter 6: Deducing the Best Execution Plan;
6.1 Robust Execution Plans;
6.2 Standard Heuristic Join Order;
6.3 Simple Examples;
6.4 A Special Case;
6.5 A Complex Example;
6.6 Special Rules for Special Cases;
6.7 Exercise (See Section A.2 for the solution to the exercise.);
Chapter 7: Diagramming and Tuning Complex SQL Queries;
7.1 Abnormal Join Diagrams;
7.2 Queries with Subqueries;
7.3 Queries with Views;
7.4 Queries with Set Operations;
7.5 Exercise (See Section A.3 for the solution to the exercise.);
Chapter 8: Why the Diagramming Method Works;
8.1 The Case for Nested Loops;
8.2 Choosing the Driving Table;
8.3 Choosing the Next Table to Join;
8.4 Summary;
Chapter 9: Special Cases;
9.1 Outer Joins;
9.2 Merged Join and Filter Indexes;
9.3 Missing Indexes;
9.4 Unfiltered Joins;
9.5 Unsolvable Problems;
Chapter 10: Outside-the-Box Solutions to Seemingly Unsolvable Problems;
10.1 When Very Fast Is Not Fast Enough;
10.2 Queries that Return Data from Too Many Rows;
10.3 Tuned Queries that Return Few Rows, Slowly;
Appendix A: Exercise Solutions;
A.1 Chapter 5 Exercise Solutions;
A.2 Chapter 6 Exercise Solution;
A.3 Chapter 7 Exercise Solution;
Appendix B: The Full Process, End to End;
B.1 Reducing the Query to a Query Diagram;
B.2 Solving the Query Diagram;
B.3 Checking the Execution Plans;
B.4 Altering the Database to Enable the Best Plan;
B.5 Altering the SQL to Enable the Best Plan;
B.6 Altering the Application;
B.7 Putting the Example in Perspective;
Glossary;
Colophon;