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