The art of SQL

Contents......Page 9
Preface......Page 11
Why Another SQL Book?......Page 12
Audience......Page 13
Contents of This Book......Page 14
Conventions Used in This Book......Page 15
Comments and Questions......Page 16
Acknowledgments......Page 17
Laying Plans......Page 19
The Relational View of Data......Page 20
The Importance of Being Normal......Page 22
Step 1: Ensure Atomicity......Page 23
Step 2: Check Dependence on the Whole Key......Page 26
Step 3: Check Attribute Independence......Page 27
To Be or Not to Be, or to Be Null......Page 29
Qualifying Boolean Columns......Page 32
Understanding Subtypes......Page 33
Stating the Obvious......Page 35
The Dangers of Excess Flexibility......Page 36
The Difficulties of Historical Data......Page 37
Design and Performance......Page 39
Processing Flow......Page 40
Centralizing Your Data......Page 41
System Complexity......Page 42
The Completed Plans......Page 43
Waging War......Page 45
Query Identification......Page 46
Stable Database Connections......Page 47
Strategy Before Tactics......Page 49
Problem Definition Before Solution......Page 50
Stable Database Schema......Page 51
Set Processing in SQL......Page 52
Action-Packed SQL Statements......Page 53
Profitable Database Accesses......Page 54
Closeness to the DBMS Kernel......Page 55
Doing Only What Is Required......Page 59
Program Logic into Queries......Page 60
Multiple Updates at Once......Page 61
Careful Use of User-Written Functions......Page 62
Succinct SQL......Page 64
Offensive Coding with SQL......Page 66
Discerning Use of Exceptions......Page 68
Tactical Dispositions......Page 73
The Identification of “Entry Points”......Page 74
Indexes and Content Lists......Page 77
Making Indexes Work......Page 78
Indexes with Functions and Conversions......Page 80
Indexes and Foreign Keys......Page 85
Multiple Indexing of the Same Columns......Page 87
System-Generated Keys......Page 88
Variability of Index Accesses......Page 90
Maneuvering......Page 93
SQL and Databases......Page 94
SQL and the Optimizer......Page 97
Limits of the Optimizer......Page 101
Criteria Defining the Result Set......Page 102
Number of Tables......Page 103
Complex queries and complex views......Page 104
Number of Other Users......Page 106
Meaning of Filtering Conditions......Page 107
Evaluation of Filtering Conditions......Page 108
Buyers of Batmobiles......Page 109
More Batmobile purchases......Page 111
Lessons to be learned from the Batmobile trade......Page 114
Querying Large Quantities of Data......Page 116
The Proportions of Retrieved Data......Page 121
Terrain......Page 123
Structural Types......Page 124
The Conflicting Goals......Page 126
Considering Indexes as Data Repositories......Page 127
Forcing Row Ordering......Page 131
Automatically Grouping Data......Page 133
Data-Driven Partitioning......Page 134
The Double-Edged Sword of Partitioning......Page 137
Partitioning and Data Distribution......Page 138
The Best Way to Partition Data......Page 139
Pre-Joining Tables......Page 141
Holy Simplicity......Page 142
The Nine Situations......Page 145
Index Usability......Page 147
Data Dispersion......Page 148
Criterion Indexability......Page 150
Small Result Set, Indirect Criteria......Page 155
Small Intersection of Broad Criteria......Page 156
Small Intersection, Indirect Broad Criteria......Page 158
Large Result Set......Page 164
Self-Joins on One Table......Page 165
Result Set Obtained by Aggregation......Page 168
Simple or Range Searching on Dates......Page 174
Using subqueries......Page 175
Using OLAP functions......Page 177
Current Values......Page 178
Result Set Predicated on Absence of Data......Page 179
Variations in Tactics......Page 185
Tree Structures Versus Master/Detail Relationships......Page 186
Practical Examples of Hierarchies......Page 188
Representing Trees in an SQL Database......Page 190
Adjacency Model......Page 192
Materialized Path Model......Page 193
Nested Sets Model (After Celko)......Page 194
Walking a Tree with SQL......Page 195
Adjacency model......Page 196
Materialized path model......Page 200
Nested sets model......Page 201
Comparing the Vandamme query under the various models......Page 202
Adjacency model......Page 203
Materialized path model......Page 204
Comparing the various models for the Highlanders query......Page 206
Modeling head counts......Page 208
Computing head counts at every level......Page 210
Propagation of Percentages Across Different Levels......Page 212
Weaknesses and Strengths......Page 217
Deceiving Criteria......Page 218
Abstract Layers......Page 220
Distributed Systems......Page 223
Dynamically Defined Search Criteria......Page 226
Designing a Simple Movie Database and the Main Query......Page 227
Right-Sizing Queries......Page 234
Wrapping SQL in PHP......Page 235
Multiple Fronts......Page 243
The Virtues of Indexes......Page 244
A Just-So Story......Page 245
Get in Line......Page 248
Concurrent Data Changes......Page 249
Locking granularity......Page 250
Lock handling......Page 252
Locking and committing......Page 254
Locking and scalability......Page 256
Insertion and contention......Page 258
DBA solutions......Page 259
Architectural solutions......Page 260
Results......Page 261
Assembly of Forces......Page 265
Increasing Volumes......Page 266
Insensitivity to volume increase......Page 268
Non-linear sensitivity to volume increases......Page 269
Putting it all together......Page 272
Disentangling subqueries......Page 274
Partitioning to the Rescue......Page 280
Data Purges......Page 281
Data Warehousing......Page 282
Facts and Dimensions: the Star Schema......Page 283
Query Tools......Page 284
Extraction, Transformation, and Loading......Page 285
Transformation......Page 286
Loading......Page 287
Querying Dimensions and Facts: Ad Hoc Reports......Page 288
The star transformation......Page 291
Emulating the star transformation......Page 292
Querying a star schema the way it is not intended to be queried......Page 294
A (Strong) Word of Caution......Page 295
Stratagems......Page 297
Turning Data Around......Page 298
Rows That Should Have Been Columns......Page 299
Columns That Should Have Been Rows......Page 302
Creating a pivot table......Page 303
Using pivot table values......Page 304
The pivot and unpivot operators......Page 306
Single Columns That Should Have Been Something Else......Page 307
First normal form on the fly......Page 308
Lifting the veil on the Chapter7 mystery path explosion......Page 311
Querying with a Variable in List......Page 312
Aggregating by Range (Bands)......Page 315
Superseding a General Case......Page 317
Selecting Rows That Match Several Items in a List......Page 319
Finding the Best Match......Page 322
Optimizer Directives......Page 323
Employment of Spies......Page 325
The Database Is Slow......Page 326
The Components of Server Load......Page 328
Defining Good Performance......Page 329
Knowing What You Get......Page 330
Checking Against Acknowledged Standards......Page 333
Defining Performance Goals......Page 334
Thinking in Business Tasks......Page 335
Execution Plans......Page 337
Our contestants......Page 338
And the winner is.........Page 340
A stubborn query......Page 341
Study of search criteria......Page 342
How Not to Execute a Query......Page 346
Hidden Complexity......Page 347
What Really Matters?......Page 348
Photo Credits......Page 351
Index......Page 353