The Art of SQL Stephane Faroult, Peter Robson

Kup polskie wydanie:

Spis treści książki

• The Art of SQL
  • SPECIAL OFFER: Upgrade this ebook with OReilly
  • Preface
    • Why Another SQL Book?
    • Audience
    • Assumptions This Book Makes
    • Contents of This Book
    • Conventions Used in This Book
    • Using Code Examples
    • Comments and Questions
    • Safari Enabled
    • Acknowledgments
  • 1. Laying Plans
    • 1.1. The Relational View of Data
    • 1.2. The Importance of Being Normal
      • 1.2.1. Step 1: Ensure Atomicity
      • 1.2.2. Step 2: Check Dependence on the Whole Key
      • 1.2.3. Step 3: Check Attribute Independence
    • 1.3. To Be or Not to Be, or to Be Null
    • 1.4. Qualifying Boolean Columns
    • 1.5. Understanding Subtypes
    • 1.6. Stating the Obvious
    • 1.7. The Dangers of Excess Flexibility
    • 1.8. The Difficulties of Historical Data
    • 1.9. Design and Performance
    • 1.10. Processing Flow
    • 1.11. Centralizing Your Data
    • 1.12. System Complexity
    • 1.13. The Completed Plans
  • 2. Waging War
    • 2.1. Query Identification
    • 2.2. Stable Database Connections
    • 2.3. Strategy Before Tactics
    • 2.4. Problem Definition Before Solution
    • 2.5. Stable Database Schema
    • 2.6. Operations Against Actual Data
    • 2.7. Set Processing in SQL
    • 2.8. Action-Packed SQL Statements
    • 2.9. Profitable Database Accesses
    • 2.10. Closeness to the DBMS Kernel
    • 2.11. Doing Only What Is Required
    • 2.12. SQL Statements Mirror Business Logic
    • 2.13. Program Logic into Queries
    • 2.14. Multiple Updates at Once
    • 2.15. Careful Use of User-Written Functions
    • 2.16. Succinct SQL
    • 2.17. Offensive Coding with SQL
    • 2.18. Discerning Use of Exceptions
  • 3. Tactical Dispositions
    • 3.1. The Identification of "Entry Points"
    • 3.2. Indexes and Content Lists
    • 3.3. Making Indexes Work
    • 3.4. Indexes with Functions and Conversions
    • 3.5. Indexes and Foreign Keys
    • 3.6. Multiple Indexing of the Same Columns
    • 3.7. System-Generated Keys
    • 3.8. Variability of Index Accesses
  • 4. Maneuvering
    • 4.1. The Nature of SQL
      • 4.1.1. SQL and Databases
      • 4.1.2. SQL and the Optimizer
      • 4.1.3. Limits of the Optimizer
    • 4.2. Five Factors Governing the Art of SQL
      • 4.2.1. Total Quantity of Data
      • 4.2.2. Criteria Defining the Result Set
      • 4.2.3. Size of the Result Set
      • 4.2.4. Number of Tables
        • 4.2.4.1. Joins
        • 4.2.4.2. Complex queries and complex views
      • 4.2.5. Number of Other Users
    • 4.3. Filtering
      • 4.3.1. Meaning of Filtering Conditions
      • 4.3.2. Evaluation of Filtering Conditions
        • 4.3.2.1. Buyers of Batmobiles
        • 4.3.2.2. More Batmobile purchases
        • 4.3.2.3. Lessons to be learned from the Batmobile trade
      • 4.3.3. Querying Large Quantities of Data
      • 4.3.4. The Proportions of Retrieved Data
  • 5. Terrain
    • 5.1. Structural Types
    • 5.2. The Conflicting Goals
    • 5.3. Considering Indexes as Data Repositories
    • 5.4. Forcing Row Ordering
    • 5.5. Automatically Grouping Data
      • 5.5.1. Round-Robin Partitioning
      • 5.5.2. Data-Driven Partitioning
    • 5.6. The Double-Edged Sword of Partitioning
    • 5.7. Partitioning and Data Distribution
    • 5.8. The Best Way to Partition Data
    • 5.9. Pre-Joining Tables
    • 5.10. Holy Simplicity
  • 6. The Nine Situations
    • 6.1. Small Result Set, Direct Specific Criteria
      • 6.1.1. Index Usability
      • 6.1.2. Query Efficiency and Index Usage
      • 6.1.3. Data Dispersion
      • 6.1.4. Criterion Indexability
    • 6.2. Small Result Set, Indirect Criteria
    • 6.3. Small Intersection of Broad Criteria
    • 6.4. Small Intersection, Indirect Broad Criteria
    • 6.5. Large Result Set
    • 6.6. Self-Joins on One Table
    • 6.7. Result Set Obtained by Aggregation
    • 6.8. Simple or Range Searching on Dates
      • 6.8.1. Many Items, Few Historical Values
        • 6.8.1.1. Using subqueries
        • 6.8.1.2. Using OLAP functions
      • 6.8.2. Many Historical Values Per Item
      • 6.8.3. Current Values
    • 6.9. Result Set Predicated on Absence of Data
  • 7. Variations in Tactics
    • 7.1. Tree Structures
      • 7.1.1. Tree Structures Versus Master/Detail Relationships
      • 7.1.2. Practical Examples of Hierarchies
    • 7.2. Representing Trees in an SQL Database
    • 7.3. Practical Implementation of Trees
      • 7.3.1. Adjacency Model
      • 7.3.2. Materialized Path Model
      • 7.3.3. Nested Sets Model (After Celko)
    • 7.4. Walking a Tree with SQL
      • 7.4.1. Top-Down Walk: The Vandamme Query
        • 7.4.1.1. Adjacency model
        • 7.4.1.2. Materialized path model
        • 7.4.1.3. Nested sets model
        • 7.4.1.4. Comparing the Vandamme query under the various models
      • 7.4.2. Bottom-Up Walk: The Highlanders Query
        • 7.4.2.1. Adjacency model
        • 7.4.2.2. Materialized path model
        • 7.4.2.3. Nested sets model
        • 7.4.2.4. Comparing the various models for the Highlanders query
    • 7.5. Aggregating Values from Trees
      • 7.5.1. Aggregation of Values Stored in Leaf Nodes
        • 7.5.1.1. Modeling head counts
        • 7.5.1.2. Computing head counts at every level
      • 7.5.2. Propagation of Percentages Across Different Levels
  • 8. Weaknesses and Strengths
    • 8.1. Deceiving Criteria
    • 8.2. Abstract Layers
    • 8.3. Distributed Systems
    • 8.4. Dynamically Defined Search Criteria
      • 8.4.1. Designing a Simple Movie Database and the Main Query
      • 8.4.2. Right-Sizing Queries
      • 8.4.3. Wrapping SQL in PHP
  • 9. Multiple Fronts
    • 9.1. The Database Engine as a Service Provider
      • 9.1.1. The Virtues of Indexes
      • 9.1.2. A Just-So Story
      • 9.1.3. Get in Line
    • 9.2. Concurrent Data Changes
      • 9.2.1. Locking
        • 9.2.1.1. Locking granularity
        • 9.2.1.2. Lock handling
        • 9.2.1.3. Locking and committing
        • 9.2.1.4. Locking and scalability
      • 9.2.2. Contention
        • 9.2.2.1. Insertion and contention
        • 9.2.2.2. DBA solutions
        • 9.2.2.3. Architectural solutions
        • 9.2.2.4. Development solutions
        • 9.2.2.5. Results
  • 10. Assembly of Forces
    • 10.1. Increasing Volumes
      • 10.1.1. Sensitivity of Operations to Volume Increases
        • 10.1.1.1. Insensitivity to volume increase
        • 10.1.1.2. Linear sensitivity to volume increases
        • 10.1.1.3. Non-linear sensitivity to volume increases
        • 10.1.1.4. Putting it all together
        • 10.1.1.5. Disentangling subqueries
      • 10.1.2. Partitioning to the Rescue
      • 10.1.3. Data Purges
    • 10.2. Data Warehousing
      • 10.2.1. Facts and Dimensions: the Star Schema
      • 10.2.2. Query Tools
      • 10.2.3. Extraction, Transformation, and Loading
        • 10.2.3.1. Data extraction
        • 10.2.3.2. Transformation
        • 10.2.3.3. Loading
        • 10.2.3.4. Integrity constraints and indexes
      • 10.2.4. Querying Dimensions and Facts: Ad Hoc Reports
        • 10.2.4.1. The star transformation
        • 10.2.4.2. Emulating the star transformation
        • 10.2.4.3. Querying a star schema the way it is not intended to be queried
      • 10.2.5. A (Strong) Word of Caution
  • 11. Stratagems
    • 11.1. Turning Data Around
      • 11.1.1. Rows That Should Have Been Columns
      • 11.1.2. Columns That Should Have Been Rows
        • 11.1.2.1. Creating a pivot table
        • 11.1.2.2. Multiplying rows with a pivot table
        • 11.1.2.3. Using pivot table values
        • 11.1.2.4. The pivot and unpivot operators
      • 11.1.3. Single Columns That Should Have Been Something Else
        • 11.1.3.1. First normal form on the fly
        • 11.1.3.2. Lifting the veil on the Chapter 7 mystery path explosion
    • 11.2. Querying with a Variable in List
    • 11.3. Aggregating by Range (Bands)
    • 11.4. Superseding a General Case
    • 11.5. Selecting Rows That Match Several Items in a List
    • 11.6. Finding the Best Match
    • 11.7. Optimizer Directives
  • 12. Employment of Spies
    • 12.1. The Database Is Slow
    • 12.2. The Components of Server Load
    • 12.3. Defining Good Performance
      • 12.3.1. Knowing What You Spend
      • 12.3.2. Knowing What You Get
      • 12.3.3. Checking Against Acknowledged Standards
      • 12.3.4. Defining Performance Goals
    • 12.4. Thinking in Business Tasks
    • 12.5. Execution Plans
      • 12.5.1. Identifying the Fastest Execution Plan
        • 12.5.1.1. Our contestants
        • 12.5.1.2. Our battle field
        • 12.5.1.3. And the winner is.. .
      • 12.5.2. Forcing the Right Execution Plan
        • 12.5.2.1. A stubborn query
        • 12.5.2.2. Study of search criteria
        • 12.5.2.3. A moral to the story
    • 12.6. Using Execution Plans Properly
      • 12.6.1. How Not to Execute a Query
      • 12.6.2. Hidden Complexity
    • 12.7. What Really Matters?
  • PHOTO CREDITS
  • About the Authors
  • About the Author
  • SPECIAL OFFER: Upgrade this ebook with OReilly

pokaż cały spis treści

ukryj recenzje