Publisher: OReillyPub Date: November 2003
ISBN: 0-596-00573-3
Pages: 336
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