SQL Tuning

Spis treści książki

• SQL Tuning
• Dedication
• SPECIAL OFFER: Upgrade this ebook with OReilly
• 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 Chapter 5 Exercise Solutions 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 Chapter 6 Exercise Solution 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 tables 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 Chapter 7 Exercise Solution 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
• SPECIAL OFFER: Upgrade this ebook with OReilly
• Copyright

pokaż cały spis treści

ukryj recenzje