- Dedication
- A Note Regarding Supplemental Files
- Foreword
- Preface
- 1. Introduction
- 2. Data-Access Basics
- 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
- 4. Controlling Execution Plans
- 4.1. Universal Techniques for Controlling Plans
- 4.1.1. Enabling Use of the Index You Want
- 4.1.2. Preventing Use of the Wrong Indexes
- 4.1.3. Enabling the Join Order You Want
- 4.1.4. Preventing Join Orders You Do Not Want
- 4.1.5. Forcing Execution Order for Outer Queries and Subqueries
- 4.1.6. Providing the Cost-Based Optimizer with Good Data
- 4.1.7. Fooling the Cost-Based Optimizer with Incorrect Data
- 4.2. Controlling Plans on Oracle
- 4.3. Controlling Plans on DB2
- 4.4. Controlling Plans on SQL Server
- 4.1. Universal Techniques for Controlling Plans
- 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.)
- 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.)
- 7. Diagramming and Tuning Complex SQL Queries
- 7.1. Abnormal Join Diagrams
- 7.1.1. Cyclic Join Graphs
- 7.1.1.1. Case 1: Two one-to-one master tables share the same detail table
- 7.1.1.2. Case 2: Master-detail tables each hold copies of a foreign key that points to the same third table’s primary key
- 7.1.1.3. Case 3: Two-node filter (nonunique on both ends) between nodes is already linked through normal joins
- 7.1.1.4. Case 4: Multipart join from two foreign keys is spread over two tables to a multipart primary key
- 7.1.1.5. Cyclic join summary
- 7.1.2. Disconnected Query Diagrams
- 7.1.3. Query Diagrams with Multiple Roots
- 7.1.4. Joins with No Primary Key
- 7.1.5. One-to-One Joins
- 7.1.6. Outer Joins
- 7.1.1. Cyclic Join Graphs
- 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.)
- 7.1. Abnormal Join Diagrams
- 8. Why the Diagramming Method Works
- 9. Special Cases
- 10. Outside-the-Box Solutions to Seemingly Unsolvable Problems
- A. Exercise Solutions
- B. The Full Process, End to End
- Glossary
- Index
- About the Author
- Colophon
- Copyright
Dedication
To Parva, Tira, and Abe, for a dream of more, and a life surpassing the dream
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.