Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
by Dan Tow
SQL Tuning
Dedication
A Note Regarding Supplemental Files
Foreword
Preface
Objectives of This Book
Audience for This Book
Structure of This Book
Conventions Used in This Book
Comments and Questions
Acknowledgments
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
2. Data-Access Basics
2.1. Caching in the Database
2.2. Tables
2.2.1. Continuous Growth
2.2.2. Purge Eldest
2.2.3. Purge, Not by Age
2.2.4. Complete Purge and Regrowth
2.3. Indexes
2.3.1. B-Tree Indexes
2.3.2. Index Costs
2.4. Uncommon Database Objects
2.4.1. Index-Organized Tables
2.4.2. Single-Table Clusters
2.4.3. Multitable Clusters
2.4.4. Partitioned Tables
2.4.5. Bit-Mapped Indexes
2.5. Single-Table Access Paths
2.5.1. Full Table Scans
2.5.2. Indexed Table Access
2.5.3. Choosing Between a Full Table Scan and Indexed Access
2.6. Calculating Selectivity
2.6.1. Filter Selectivity
2.6.2. Index Range-Condition Selectivity
2.6.3. Selectivity on Table Rows Reached from the Index
2.6.4. Combining Indexes
2.7. Joins
2.7.1. Join Types
2.7.1.1. Inner joins
2.7.1.2. Outer joins
2.7.2. Join Execution Methods
2.7.2.1. Nested-loops joins
2.7.2.2. Hash joins
2.7.2.3. Sort-merge joins
2.7.2.4. Join methods summary
3. Viewing and Interpreting Execution Plans
3.1. Reading Oracle Execution Plans
3.1.1. Prerequisites
3.1.2. The Underlying Process of Displaying Execution Plans
3.1.3. The Practical Process of Displaying Execution Plans
3.1.4. Robust Execution Plans
3.1.4.1. How to interpret the plan
3.1.4.2. Narrative interpretation of the execution plan
3.1.5. Nonrobust Execution Plans
3.1.6. Complex Execution Plans
3.2. Reading DB2 Execution Plans
3.2.1. Prerequisites
3.2.2. The Underlying Process of Displaying Execution Plans
3.2.3. The Practical Process of Displaying Execution Plans
3.2.4. Robust Execution Plans
3.2.4.1. How to interpret the plan
3.2.4.2. Narrative interpretation of the execution plan
3.2.5. Nonrobust Execution Plans
3.2.6. Complex Execution Plans
3.3. Reading SQL Server Execution Plans
3.3.1. Displaying Execution Plans
3.3.1.1. Displaying execution plans graphically
3.3.1.2. Displaying execution plans textually
3.3.2. How to Interpret the Plan
3.3.3. Narrative Interpretation of the Execution Plan
3.3.4. Interpreting Nonrobust Execution Plans
3.3.5. Complex 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.3.1. Outer joins
4.1.3.2. Missing redundant join conditions
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.2.1. Controlling the Choice of Oracle Optimizer
4.2.2. Controlling Oracle Rule-Based Execution Plans
4.2.3. Controlling Oracle Cost-Based Execution Plans
4.2.3.1. Oracle cost-based optimizer prerequisites
4.2.3.2. General hint syntax
4.2.3.3. Approaches to tuning with hints
4.2.3.4. Table-access hints
4.2.3.5. Execution-order hints
4.2.3.6. Join-method hints
4.2.3.7. Example
4.3. Controlling Plans on DB2
4.3.1. DB2 Optimization Prerequisites
4.3.2. Choosing the Optimization Level
4.3.3. Modifying the Query
4.3.3.1. Place inner joins first in your FROM clause
4.3.3.2. Prevent too many outer joins from parsing at once
4.3.3.3. Let DB2 know when to optimize the cost of reading just the first few rows
4.4. Controlling Plans on SQL Server
4.4.1. SQL Server Optimization Prerequisites
4.4.2. Modifying the Query
4.4.3. Hint Examples
4.4.4. Using FORCEPLAN
5. Diagramming Simple SQL Queries
5.1. Why a New Method?
5.2. Full Query Diagrams
5.2.1. Information Included in Query Diagrams
5.2.1.1. Nodes
5.2.1.2. Links
5.2.1.3. Underlined numbers
5.2.1.4. Nonunderlined numbers
5.2.2. What Query Diagrams Leave Out
5.2.2.1. Select lists
5.2.2.2. Ordering and aggregation
5.2.2.3. Table names
5.2.2.4. Detailed join conditions
5.2.2.5. Absolute table sizes (as opposed to relative sizes)
5.2.2.6. Filter condition details
5.2.3. When Query Diagrams Help the Most
5.2.4. Conceptual Demonstration of Query Diagrams in Use
5.2.5. Creating Query Diagrams
5.2.6. A More Complex Example
5.2.6.1. Diagram joins to the first focus
5.2.6.2. Diagram joins from the first focus
5.2.6.3. Change focus and repeat
5.2.6.4. Compute filter and join ratios
5.2.7. Shortcuts
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.3.1. Join Order for an Eight-Way Join
6.3.2. Completing the Solution for an Eight-Way Join
6.3.3. A Complex 17-Way Join
6.4. A Special Case
6.4.1. The Oracle Solution
6.4.2. Solving the Special Case Outside of Oracle
6.5. A Complex Example
6.6. Special Rules for Special Cases
6.6.1. Safe Cartesian Products
6.6.2. Detail Join Ratios Close to 1.0
6.6.3. Join Ratios Less than 1.0
6.6.3.1. Rules for join ratios less than 1.0
6.6.3.2. Detail join ratios less than 1.0
6.6.3.3. Optimizing detail join ratios less than 1.0 with the rules
6.6.3.4. Master join ratios less than 1.0
6.6.4. Close Filter Ratios
6.6.5. Cases to Consider Hash Joins
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.3.1. Case 1: Missing join conditions
7.1.3.2. Case 2: Breaking the Cartesian product into multiple queries
7.1.3.3. Case 3: Root detail tables that are usually no more than one-to-one
7.1.3.4. Case 4: Converting an existence check to an explicit subquery
7.1.4. Joins with No Primary Key
7.1.5. One-to-One Joins
7.1.5.1. One-to-one join to a subset table
7.1.5.2. Exact one-to-one joins
7.1.5.3. One-to-one join to a much smaller subset
7.1.5.4. One-to-one joins with hidden join filters in both directions
7.1.5.5. Conventions to display one-to-one joins
7.1.6. Outer Joins
7.1.6.1. Filtered outer joins
7.1.6.2. Outer joins leading to inner joins
7.1.6.3. Outer joins pointing toward the detail table
7.1.6.4. Outer joins to a detail table with a filter
7.2. Queries with Subqueries
7.2.1. Diagramming Queries with Subqueries
7.2.1.1. Diagramming EXISTS subqueries
7.2.1.2. Diagramming NOT EXISTS subqueries
7.2.2. Tuning Queries with Subqueries
7.3. Queries with Views
7.3.1. Diagramming View-Using Queries
7.3.2. Tuning Queries with Views
7.3.2.1. Outer joins to views
7.3.2.2. Redundant reads in view-using queries
7.3.2.3. Unnecessary nodes and joins
7.4. Queries with Set Operations
7.5. Exercise (See Section A.3 for the solution to the exercise.)
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.3.1. Accounting for Unequal Per-Row Costs
8.3.2. Accounting for Benefits from Later Joins
8.3.3. When to Choose Early Joins to Upstream Nodes
8.4. Summary
9. Special Cases
9.1. Outer Joins
9.1.1. Steps for Normal Outer Join Order Optimization
9.1.2. Example
9.2. Merged Join and Filter Indexes
9.3. Missing Indexes
9.4. Unfiltered Joins
9.5. Unsolvable Problems
10. Outside-the-Box Solutions to Seemingly Unsolvable Problems
10.1. When Very Fast Is Not Fast Enough
10.1.1. Caching to Avoid Repeated Queries
10.1.2. Consolidated Queries
10.1.3. Merging Repeated Queries into a Preexisting Query
10.2. Queries that Return Data from Too Many Rows
10.2.1. Large Online Queries
10.2.2. Large Batch Reports
10.2.2.1. Reasons for large reports
10.2.2.2. Ways reports are triggered
10.2.2.3. Reasons batch performance is a concern
10.2.2.4. Report information types
10.2.2.5. Solutions
10.2.3. Aggregations of Many Details
10.2.4. Middleware Processes Handling Too Many Rows
10.3. Tuned Queries that Return Few Rows, Slowly
10.3.1. Why Queries Sometimes Read Many Rows to Return Few
10.3.2. Optimizing Queries with Distributed Filters
A. Exercise Solutions
A.1. Chapter 5 Exercise Solutions
A.1.1. Exercise 1
A.1.2. Exercise 2
A.1.3. Exercise 3
A.1.4. Exercise 4
A.1.5. Exercise 5
A.1.6. Exercise 6
A.2. Chapter 6 Exercise Solution
A.3. Chapter 7 Exercise Solution
B. The Full Process, End to End
B.1. Reducing the Query to a Query Diagram
B.1.1. Creating the Query Skeleton
B.1.2. Creating a Simplified Query Diagram
B.1.3. Creating a Full Query Diagram
B.2. Solving the Query Diagram
B.3. Checking the Execution Plans
B.3.1. Getting the Oracle Execution Plan
B.3.2. Getting the DB2 Execution Plan
B.3.3. Getting the SQL Server Execution Plan
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
Index
About the Author
Colophon
Copyright
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
O'Reilly Strata Conference
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset