Art of SQL

Name: Art of SQL
Brand: O'Reilly
Price: 63.99 EUR
Availability: OnlineOnly

Stephane Faroult(Autor*in)

O'Reilly (Verlag)

1. Auflage

Erschienen am 10. März 2006

372 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

978-0-596-55536-8 (ISBN)

63,99 €inkl. 7% MwSt.

Systemvoraussetzungen

für ePUB mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

For all the buzz about trendy IT techniques, data processing is still at the core of our systems, especially now that enterprises all over the world are confronted with exploding volumes of data. Database performance has become a major headache, and most IT departments believe that developers should provide simple SQL code to solve immediate problems and let DBAs tune any &quote;bad SQL&quote; later.In The Art of SQL, author and SQL expert Stephane Faroult argues that this &quote;safe approach&quote; only leads to disaster. His insightful book, named after Art of War by Sun Tzu, contends that writing quick inefficient code is sweeping the dirt under the rug. SQL code may run for 5 to 10 years, surviving several major releases of the database management system and on several generations of hardware. The code must be fast and sound from the start, and that requires a firm understanding of SQL and relational theory.The Art of SQL offers best practices that teach experienced SQL users to focus on strategy rather than specifics. Faroult's approach takes a page from Sun Tzu's classic treatise by viewing database design as a military campaign. You need knowledge, skills, and talent. Talent can't be taught, but every strategist from Sun Tzu to modern-day generals believed that it can be nurtured through the experience of others. They passed on their experience acquired in the field through basic principles that served as guiding stars amid the sound and fury of battle. This is what Faroult does with SQL.Like a successful battle plan, good architectural choices are based on contingencies. What if the volume of this or that table increases unexpectedly? What if, following a merger, the number of users doubles? What if you want to keep several years of data online? Faroult's way of looking at SQL performance may be unconventional and unique, but he's deadly serious about writing good SQL and using SQL well. The Art of SQL is not a cookbook, listing problems and giving recipes. The aim is to get you-and your manager-to raise good questions.

Weitere Details

Weitere Ausgaben

Inhalt

Intro
The Art of SQL
SPECIAL OFFER: Upgrade this ebook with O'Reilly
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 O'Reilly

Systemvoraussetzungen

Als PDF speichern Als Link merken