book

SQL Tuning

Name: SQL Tuning
Author: Dan Tow
ISBN: 9780596005733

by Dan Tow

November 2003

Intermediate to advanced

336 pages

11h 35m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Dedication
A Note Regarding Supplemental Files
Foreword
Preface
Objectives of This BookAudience for This BookStructure of This BookConventions Used in This BookComments and QuestionsAcknowledgments
1. Introduction
1.1. Why Tune SQL?1.2. Who Should Tune SQL?1.3. How This Book Can Help1.4. A Bonus1.5. Outside-the-Box Solutions
2. Data-Access Basics
2.1. Caching in the Database2.2. Tables2.2.1. Continuous Growth2.2.2. Purge Eldest2.2.3. Purge, Not by Age2.2.4. Complete Purge and Regrowth2.3. Indexes2.3.1. B-Tree Indexes2.3.2. Index Costs2.4. Uncommon Database Objects2.4.1. Index-Organized Tables2.4.2. Single-Table Clusters2.4.3. Multitable Clusters2.4.4. Partitioned Tables2.4.5. Bit-Mapped Indexes2.5. Single-Table Access Paths2.5.1. Full Table Scans2.5.2. Indexed Table Access2.5.3. Choosing Between a Full Table Scan and Indexed Access2.6. Calculating Selectivity2.6.1. Filter Selectivity2.6.2. Index Range-Condition Selectivity2.6.3. Selectivity on Table Rows Reached from the Index2.6.4. Combining Indexes2.7. Joins2.7.1. Join Types2.7.1.1. Inner joins2.7.1.2. Outer joins2.7.2. Join Execution Methods2.7.2.1. Nested-loops joins2.7.2.2. Hash joins2.7.2.3. Sort-merge joins2.7.2.4. Join methods summary
3. Viewing and Interpreting Execution Plans
3.1. Reading Oracle Execution Plans3.1.1. Prerequisites3.1.2. The Underlying Process of Displaying Execution Plans3.1.3. The Practical Process of Displaying Execution Plans3.1.4. Robust Execution Plans3.1.4.1. How to interpret the plan3.1.4.2. Narrative interpretation of the execution plan3.1.5. Nonrobust Execution Plans3.1.6. Complex Execution Plans3.2. Reading DB2 Execution Plans3.2.1. Prerequisites3.2.2. The Underlying Process of Displaying Execution Plans3.2.3. The Practical Process of Displaying Execution Plans3.2.4. Robust Execution Plans3.2.4.1. How to interpret the plan3.2.4.2. Narrative interpretation of the execution plan3.2.5. Nonrobust Execution Plans3.2.6. Complex Execution Plans3.3. Reading SQL Server Execution Plans3.3.1. Displaying Execution Plans3.3.1.1. Displaying execution plans graphically3.3.1.2. Displaying execution plans textually3.3.2. How to Interpret the Plan3.3.3. Narrative Interpretation of the Execution Plan3.3.4. Interpreting Nonrobust Execution Plans3.3.5. Complex Execution Plans
4. Controlling Execution Plans
4.1. Universal Techniques for Controlling Plans4.1.1. Enabling Use of the Index You Want4.1.2. Preventing Use of the Wrong Indexes4.1.3. Enabling the Join Order You Want4.1.3.1. Outer joins4.1.3.2. Missing redundant join conditions4.1.4. Preventing Join Orders You Do Not Want4.1.5. Forcing Execution Order for Outer Queries and Subqueries4.1.6. Providing the Cost-Based Optimizer with Good Data4.1.7. Fooling the Cost-Based Optimizer with Incorrect Data4.2. Controlling Plans on Oracle4.2.1. Controlling the Choice of Oracle Optimizer4.2.2. Controlling Oracle Rule-Based Execution Plans4.2.3. Controlling Oracle Cost-Based Execution Plans4.2.3.1. Oracle cost-based optimizer prerequisites4.2.3.2. General hint syntax4.2.3.3. Approaches to tuning with hints4.2.3.4. Table-access hints4.2.3.5. Execution-order hints4.2.3.6. Join-method hints4.2.3.7. Example4.3. Controlling Plans on DB24.3.1. DB2 Optimization Prerequisites4.3.2. Choosing the Optimization Level4.3.3. Modifying the Query4.3.3.1. Place inner joins first in your FROM clause4.3.3.2. Prevent too many outer joins from parsing at once4.3.3.3. Let DB2 know when to optimize the cost of reading just the first few rows4.4. Controlling Plans on SQL Server4.4.1. SQL Server Optimization Prerequisites4.4.2. Modifying the Query4.4.3. Hint Examples4.4.4. Using FORCEPLAN
5. Diagramming Simple SQL Queries
5.1. Why a New Method?5.2. Full Query Diagrams5.2.1. Information Included in Query Diagrams5.2.1.1. Nodes5.2.1.2. Links5.2.1.3. Underlined numbers5.2.1.4. Nonunderlined numbers5.2.2. What Query Diagrams Leave Out5.2.2.1. Select lists5.2.2.2. Ordering and aggregation5.2.2.3. Table names5.2.2.4. Detailed join conditions5.2.2.5. Absolute table sizes (as opposed to relative sizes)5.2.2.6. Filter condition details5.2.3. When Query Diagrams Help the Most5.2.4. Conceptual Demonstration of Query Diagrams in Use5.2.5. Creating Query Diagrams5.2.6. A More Complex Example5.2.6.1. Diagram joins to the first focus5.2.6.2. Diagram joins from the first focus5.2.6.3. Change focus and repeat5.2.6.4. Compute filter and join ratios5.2.7. Shortcuts5.3. Interpreting Query Diagrams5.4. Simplified Query Diagrams5.5. Exercises (See Section A.1 for the solution to each exercise.)
6. Deducing the Best Execution Plan
6.1. Robust Execution Plans6.2. Standard Heuristic Join Order6.3. Simple Examples6.3.1. Join Order for an Eight-Way Join6.3.2. Completing the Solution for an Eight-Way Join6.3.3. A Complex 17-Way Join6.4. A Special Case6.4.1. The Oracle Solution6.4.2. Solving the Special Case Outside of Oracle6.5. A Complex Example6.6. Special Rules for Special Cases6.6.1. Safe Cartesian Products6.6.2. Detail Join Ratios Close to 1.06.6.3. Join Ratios Less than 1.06.6.3.1. Rules for join ratios less than 1.06.6.3.2. Detail join ratios less than 1.06.6.3.3. Optimizing detail join ratios less than 1.0 with the rules6.6.3.4. Master join ratios less than 1.06.6.4. Close Filter Ratios6.6.5. Cases to Consider Hash Joins6.7. Exercise (See Section A.2 for the solution to the exercise.)

7. Diagramming and Tuning Complex SQL Queries
7.1. Abnormal Join Diagrams7.1.1. Cyclic Join Graphs7.1.1.1. Case 1: Two one-to-one master tables share the same detail table7.1.1.2. Case 2: Master-detail tables each hold copies of a foreign key that points to the same third table’s primary key7.1.1.3. Case 3: Two-node filter (nonunique on both ends) between nodes is already linked through normal joins7.1.1.4. Case 4: Multipart join from two foreign keys is spread over two tables to a multipart primary key7.1.1.5. Cyclic join summary7.1.2. Disconnected Query Diagrams7.1.3. Query Diagrams with Multiple Roots7.1.3.1. Case 1: Missing join conditions7.1.3.2. Case 2: Breaking the Cartesian product into multiple queries7.1.3.3. Case 3: Root detail tables that are usually no more than one-to-one7.1.3.4. Case 4: Converting an existence check to an explicit subquery7.1.4. Joins with No Primary Key7.1.5. One-to-One Joins7.1.5.1. One-to-one join to a subset table7.1.5.2. Exact one-to-one joins7.1.5.3. One-to-one join to a much smaller subset7.1.5.4. One-to-one joins with hidden join filters in both directions7.1.5.5. Conventions to display one-to-one joins7.1.6. Outer Joins7.1.6.1. Filtered outer joins7.1.6.2. Outer joins leading to inner joins7.1.6.3. Outer joins pointing toward the detail table7.1.6.4. Outer joins to a detail table with a filter7.2. Queries with Subqueries7.2.1. Diagramming Queries with Subqueries7.2.1.1. Diagramming EXISTS subqueries7.2.1.2. Diagramming NOT EXISTS subqueries7.2.2. Tuning Queries with Subqueries7.3. Queries with Views7.3.1. Diagramming View-Using Queries7.3.2. Tuning Queries with Views7.3.2.1. Outer joins to views7.3.2.2. Redundant reads in view-using queries7.3.2.3. Unnecessary nodes and joins7.4. Queries with Set Operations7.5. Exercise (See Section A.3 for the solution to the exercise.)
8. Why the Diagramming Method Works
8.1. The Case for Nested Loops8.2. Choosing the Driving Table8.3. Choosing the Next Table to Join8.3.1. Accounting for Unequal Per-Row Costs8.3.2. Accounting for Benefits from Later Joins8.3.3. When to Choose Early Joins to Upstream Nodes8.4. Summary
9. Special Cases
9.1. Outer Joins9.1.1. Steps for Normal Outer Join Order Optimization9.1.2. Example9.2. Merged Join and Filter Indexes9.3. Missing Indexes9.4. Unfiltered Joins9.5. Unsolvable Problems
10. Outside-the-Box Solutions to Seemingly Unsolvable Problems
10.1. When Very Fast Is Not Fast Enough10.1.1. Caching to Avoid Repeated Queries10.1.2. Consolidated Queries10.1.3. Merging Repeated Queries into a Preexisting Query10.2. Queries that Return Data from Too Many Rows10.2.1. Large Online Queries10.2.2. Large Batch Reports10.2.2.1. Reasons for large reports10.2.2.2. Ways reports are triggered10.2.2.3. Reasons batch performance is a concern10.2.2.4. Report information types10.2.2.5. Solutions10.2.3. Aggregations of Many Details10.2.4. Middleware Processes Handling Too Many Rows10.3. Tuned Queries that Return Few Rows, Slowly10.3.1. Why Queries Sometimes Read Many Rows to Return Few10.3.2. Optimizing Queries with Distributed Filters
A. Exercise Solutions
A.1. Chapter 5 Exercise SolutionsA.1.1. Exercise 1A.1.2. Exercise 2A.1.3. Exercise 3A.1.4. Exercise 4A.1.5. Exercise 5A.1.6. Exercise 6A.2. Chapter 6 Exercise SolutionA.3. Chapter 7 Exercise Solution
B. The Full Process, End to End
B.1. Reducing the Query to a Query DiagramB.1.1. Creating the Query SkeletonB.1.2. Creating a Simplified Query DiagramB.1.3. Creating a Full Query DiagramB.2. Solving the Query DiagramB.3. Checking the Execution PlansB.3.1. Getting the Oracle Execution PlanB.3.2. Getting the DB2 Execution PlanB.3.3. Getting the SQL Server Execution PlanB.4. Altering the Database to Enable the Best PlanB.5. Altering the SQL to Enable the Best PlanB.6. Altering the ApplicationB.7. Putting the Example in Perspective
Glossary
Index
About the Author
Colophon
Copyright

Content preview from SQL Tuning

Chapter 6. Deducing the Best Execution Plan

For Tyme ylost may nought recovered be.

—Geoffrey Chaucer Troilus and Criseyde

Just as reducing a word problem to abstract mathematics is usually the hardest part of solving the problem, you will usually find that producing the query diagram is harder than deducing the best execution plan from the query diagram. Now that you know the hard part, how to translate a query into a query diagram, I demonstrate the easy part. There are several questions you need to answer to fully describe the optimum execution plan for a query:

How do you reach each table in the execution plan, with a full table scan or one or more indexes, and which indexes do you use, if any?
How do you join the tables in the execution plan?
In what order do you join the tables in the execution plan?

Out of these three questions, I make a case that the only hard question, and the main point of the query diagram, is the question of join order. If you begin by finding the optimum join order, which is nearly decoupled from the other questions, you will find that answers to the other two questions are usually obvious. In the worst cases, you might need to try experiments to answer the other two questions, but these will require at most one or two experiments per table. If you did not have a systematic way to answer the join-order question, you would require potentially billions of experiments to find the best plan.

Robust Execution Plans

A subset of all possible execution plans can be ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596005733Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SQL Tuning

by Dan Tow

Chapter 6. Deducing the Best Execution Plan

Robust Execution Plans

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.