Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
by Peter Robson, Stephane Faroult
The Art of SQL
Dedication
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
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
The Art of SQL
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