Muutke küpsiste eelistusi

Relational Database Index Design and the Optimizers: DB2, Oracle, SQL Server, et al. [Kõva köide]

3.80/5 (28 hinnangut Goodreads-ist)
,
  • Formaat: Hardback, 328 pages, kõrgus x laius x paksus: 244x162x19 mm, kaal: 588 g, Drawings: 100 B&W, 0 Color
  • Ilmumisaeg: 16-Sep-2005
  • Kirjastus: Wiley-Interscience
  • ISBN-10: 0471719994
  • ISBN-13: 9780471719991
Teised raamatud teemal:
Relational Database Index Design and the Optimizers: DB2, Oracle, SQL Server, et al.Suurem pilt
  • Formaat: Hardback, 328 pages, kõrgus x laius x paksus: 244x162x19 mm, kaal: 588 g, Drawings: 100 B&W, 0 Color
  • Ilmumisaeg: 16-Sep-2005
  • Kirjastus: Wiley-Interscience
  • ISBN-10: 0471719994
  • ISBN-13: 9780471719991
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9780471719991.html
Märksõnad:
Improve the performance of relational databases with indexes designed for today's hardware

Over the last few years, hardware and software have advanced beyond all recognition, so it's hardly surprising that relational database performance now receives much less attention. Unfortunately, the reality is that the improved hardware hasn't kept pace with the ever-increasing quantity of data processed today. Although disk packing densities have increased enormously, making storage costs extremely low and sequential read very fast, random reads are still painfully slow. Many of the old design recommendations are therefore no longer valid-the optimal point of indexing has come a long way. Consequently many of the old problems haven't actually gone away-they have simply changed their appearance.

This book provides an easy but effective approach to the design of indexes and tables. Using lots of examples and case studies, the authors describe how the DB2, Oracle, and SQL Server optimizers determine how to access data, and how CPU and response times for the resulting access paths can be quickly estimated. This enables comparisons to be made of the various designs, and helps you choose available choices for the most appropriate design.

This book is intended for anyone who wants to understand the issues of SQL performance or how to design tables and indexes effectively. With this title, readers with many years of experience of relational systems will be able to better grasp the implications that have been brought into play by the introduction of new hardware.

An Instructor's Manual presenting detailed solutions to all the problems in the book is available online from the Wiley editorial department.

An Instructor Support FTP site is also available.

Arvustused

"I recommend this book to all those who have anything to do with database performance. It is a must-read for all database administrations, database designers, performance-tuning specialists, and application programmers" (Computing Reviews.com, November 20, 2005)

Preface xv
Introduction
1(10)
Another Book About SQL Performance!
1(2)
Inadequate Indexing
3(1)
Myths and Misconceptions
4(4)
Myth 1: No More Than Five Index Levels
5(1)
Myth 2: No More Than Six Indexes per Table
6(1)
Myth 3: Volatile Columns Should Not Be Indexed
6(1)
Example
7(1)
Disk Drive Utilization
7(1)
Systematic Index Design
8(3)
Table and Index Organization
11(18)
Introduction
11(1)
Index and Table Pages
12(1)
Index Rows
12(1)
Index Structure
13(1)
Table Rows
13(1)
Buffer Pools and Disk I/Os
13(7)
Reads from the DBMS Buffer Pool
14(1)
Random I/O from Disk Drives
14(1)
Reads from the Disk Server Cache
15(1)
Sequential Reads from Disk Drives
16(1)
Assisted Random Reads
16(3)
Assisted Sequential Reads
19(1)
Synchronous and Asynchronous I/Os
19(1)
Hardware Specifics
20(1)
DBMS Specifics
21(8)
Pages
21(1)
Table Clustering
22(1)
Index Rows
23(1)
Table Rows
23(1)
Index-Only Tables
23(1)
Page Adjacency
24(1)
Alternatives to B-tree Indexes
25(1)
Many Meanings of Cluster
26(3)
SQL Processing
29(18)
Introduction
29(1)
Predicates
30(1)
Optimizers and Access Paths
30(7)
Index Slices and Matching Columns
31(1)
Index Screening and Screening Columns
32(1)
Access Path Terminology
33(1)
Monitoring the Optimizer
34(1)
Helping the Optimizer (Statistics)
34(1)
Helping the Optimizer (Number of FETCH Calls)
35(1)
When the Access Path Is Chosen
36(1)
Filter Factors
37(5)
Filter Factors for Compound Predicates
37(2)
Impact of Filter Factors on Index Design
39(3)
Materializing the Result Rows
42(2)
Cursor Review
42(1)
Alternative 1: FETCH Call Materializes One Result Row
43(1)
Alternative 2: Early Materialization
44(1)
What Every Database Designer Should Remember
44(1)
Exercises
44(3)
Deriving the Ideal Index for a Select
47(16)
Introduction
47(1)
Basic Assumptions for Disk and CPU Times
48(1)
Inadequate Index
48(1)
Three-Star Index---The Ideal Index for a Select
49(5)
How the Stars Are Assigned
50(2)
Range Predicates and a Three-Star Index
52(2)
Algorithm to Derive the Best Index for a Select
54(2)
Candidate A
54(1)
Candidate B
55(1)
Sorting Is Fast Today---Why Do We Need Candidate B?
55(1)
Ideal Index for Every Select?
56(2)
Totally Superfluous Indexes
57(1)
Practically Superfluous Indexes
57(1)
Possibly Superfluous Indexes
58(1)
Cost of an Additional Index
58(4)
Response Time
58(1)
Drive Load
59(2)
Disk Space
61(1)
Recommendation
62(1)
Exercises
62(1)
Proactive Index Design
63(24)
Detection of Inadequate Indexing
63(1)
Basic Question (BQ)
63(2)
Warning
64(1)
Quick Upper-Bound Estimate (QUBE)
65(10)
Service Time
65(1)
Queuing Time
66(1)
Essential Concept: Touch
67(2)
Counting Touches
69(1)
FETCH Processing
70(1)
QUBE Examples for the Main Access Types
71(4)
Cheapest Adequate Index or Best Possible Index: Example 1
75(7)
Basic Question for the Transaction
78(1)
Quick Upper-Bound Estimate for the Transaction
78(1)
Cheapest Adequate Index or Best Possible Index
79(1)
Best Index for the Transaction
79(1)
Semifat Index (Maximum Index Screening)
80(1)
Fat Index (Index Only)
80(2)
Cheapest Adequate Index or Best Possible Index: Example 2
82(4)
Basic Question and QUBE for the Range Transaction
82(1)
Best Index for the Transaction
83(1)
Semifat Index (Maximum Index Screening)
84(1)
Fat Index (Index Only)
85(1)
When to Use the QUBE
86(1)
Factors Affecting the Index Design Process
87(18)
I/O Time Estimate Verification
87(1)
Multiple Thin Index Slices
88(3)
Simple Is Beautiful (and Safe)
90(1)
Difficult Predicates
91(3)
Like Predicate
91(1)
Or Operator and Boolean Predicates
92(1)
In Predicate
93(1)
Filter Factor Pitfall
94(2)
Filter Factor Pitfall Example
96(6)
Best Index for the Transaction
99(1)
Semifat Index (Maximum Index Screening)
100(1)
Fat Index (Index Only)
101(1)
Summary
101(1)
Exercises
102(3)
Reactive Index Design
105(30)
Introduction
105(1)
Explain Describes the Selected Access Paths
106(2)
Full Table Scan or Full Index Scan
106(1)
Sorting Result Rows
106(1)
Cost Estimate
107(1)
DBMS-Specific Explain Options and Restrictions
108(1)
Monitoring Reveals the Reality
108(3)
Evolution of Performance Monitors
109(2)
LRT-Level Exception Monitoring
111(12)
Averages per Program Are Not Sufficient
111(1)
Exception Report Example: One Line per Spike
111(1)
Culprits and Victims
112(2)
Promising and Unpromising Culprits
114(1)
Promising Culprits
114(2)
Tuning Potential
116(4)
Unpromising Culprits
120(1)
Victims
121(2)
Finding the Slow SQL Calls
123(1)
Call-Level Exception Monitoring
123(8)
Oracle Example
126(3)
SQL Server Example
129(2)
Conclusion
131(1)
DBMS-Specific Monitoring Issues
131(2)
Spike Report
132(1)
Exercises
133(2)
Indexing for Table Joins
135(50)
Introduction
135(1)
Two Simple Joins
136(3)
Example 8.1: Customer Outer Table
137(1)
Example 8.2: Invoice Outer Table
138(1)
Impact of Table Access Order on Index Design
139(19)
Case Study
140(3)
Current Indexes
143(6)
Ideal Indexes
149(4)
Ideal Indexes with One Screen per Transaction Materialized
153(4)
Ideal Indexes with One Screen per Transaction Materialized and FF Pitfall
157(1)
Basic Join Question (BJQ)
158(3)
Conclusion: Nested-Loop Join
160(1)
Predicting the Table Access Order
161(2)
Merge Scan Joins and Hash Joins
163(7)
Merge Scan Join
163(1)
Example 8.3: Merge Scan Join
163(2)
Hash Joins
165(1)
Program C: MS/HJ Considered by the Optimizer (Current Indexes)
166(1)
Ideal Indexes
167(3)
Nested-Loop Joins Versus MS/HJ and Ideal Indexes
170(1)
Nested-Loop Joins Versus MS/HJ
170(1)
Ideal Indexes for Joins
171(1)
Joining More Than Two Tables
171(3)
Why Joins Often Perform Poorly
174(1)
Fuzzy Indexing
174(1)
Optimizer May Choose the Wrong Table Access Order
175(1)
Optimistic Table Design
175(1)
Designing Indexes for Subqueries
175(1)
Designing Indexes for Unions
176(1)
Table Design Considerations
176(7)
Redundant Data
176(4)
Unconscious Table Design
180(3)
Exercises
183(2)
Star Join Considerations
185(10)
Introduction
185(2)
Indexes on Dimension Tables
187(1)
Huge Impact of the Table Access Order
188(2)
Indexes on Fact Tables
190(2)
Summary Tables
192(3)
Multiple Index Access
195(8)
Introduction
195(1)
Index ANDing
195(4)
Index ANDing with Query Tables
197(1)
Multiple Index Access and Fact Tables
198(1)
Multiple Index Access with Bitmap Indexes
198(1)
Index ORing
199(1)
Index Join
200(1)
Exercises
201(2)
Indexes and Reorganization
203(28)
Physical Structure of a B-Tree Index
203(1)
How the DBMS Finds an Index Row
204(1)
What Happens When a Row Is Inserted?
205(1)
Are Leaf Page Splits Serious?
206(2)
When Should an Index Be Reorganized?
208(8)
Insert Patterns
208(8)
Volatile Index Columns
216(2)
Long Index Rows
218(1)
Example: Order-Sensitive Batch Job
219(4)
Table Disorganization (with a Clustering Index)
222(1)
Table Disorganization (Without Clustering Index Starting with CNO)
223(1)
Table Rows Stored in Leaf Pages
223(2)
SQL Server
223(1)
Oracle
224(1)
Cost of Index Reorganization
225(1)
Split Monitoring
226(1)
Summary
227(4)
DBMS-Specific Indexing Restrictions
231(6)
Introduction
231(1)
Number of Index Columns
231(1)
Total Length of the Index Columns
232(1)
Variable-Length Columns
232(1)
Number of Indexes per Table
232(1)
Maximum Index Size
232(1)
Index Locking
232(1)
Index Row Suppression
233(1)
DBMS Index Creation Examples
234(3)
DBMS-Specific Indexing Options
237(8)
Introduction
237(1)
Index Row Suppression
237(1)
Additional Index Columns After the Index Key
238(2)
Constraints to Enforce Uniqueness
240(1)
DBMS Able to Read an Index in Both Directions
240(1)
Index Key Truncation
241(1)
Function-Based Indexes
241(1)
Index Skip Scan
242(1)
Block Indexes
243(1)
Data-Partitioned Secondary Indexes
243(1)
Exercises
244(1)
Optimizers Are Not Perfect
245(22)
Introduction
245(1)
Optimizers Do Not Always See the Best Alternative
246(6)
Matching and Screening Problems
246(1)
Non-BT
247(3)
Unnecessary Sort
250(1)
Unnecessary Table Touches
251(1)
Optimizers' Cost Estimates May Be Very Wrong
252(7)
Range Predicates with Host Variables
252(1)
Skewed Distribution
253(2)
Correlated Columns
255(1)
Cautionary Tale of Partial Index Keys
256(3)
Cost Estimate Formulas
259(6)
Estimating I/O Time
259(2)
Estimating CPU Time
261(1)
Helping the Optimizer with Estimate-Related Problems
261(4)
Do Optimizer Problems Affect Index Design?
265(1)
Exercises
265(2)
Additional Estimation Considerations
267(22)
Assumptions Behind the QUBE Formula
267(1)
Nonleaf Index Pages in Memory
268(4)
Example
268(1)
Impact of the Disk Server Read Cache
269(1)
Buffer Subpools
270(2)
Long Rows
272(1)
Slow Sequential Read
272(1)
When the Actual Response Time Can Be Much Shorter Than the QUBE
272(6)
Leaf Pages and Table Pages Remain in the Buffer Pool
273(2)
Identifying These Cheap Random Touches
275(1)
Assisted Random Reads
275(3)
Assisted Sequential Reads
278(1)
Estimating CPU Time (CQUBE)
278(4)
CPU Time per Sequential Touch
278(1)
CPU Time per Random Touch
279(2)
CPU Time per FETCH Call
281(1)
CPU Time per Sorted Row
282(1)
CPU Estimation Examples
282(7)
Fat Index or Ideal Index
283(1)
Nested-Loop Join (and Denormalization) or MS/HJ
283(3)
Merge Scan and Hash Join Comparison
286(1)
Skip-Sequential
287(1)
CPU Time Still Matters
288(1)
Organizing the Index Design Process
289(6)
Introduction
289(1)
Computer-Assisted Index Design
290(2)
Nine Steps Toward Excellent Indexes
292(3)
References
295(2)
Glossary
297(8)
Index Design Approach
297(2)
General
299(6)
Index 305


Tapio Lahdenmaki received his M.Sc. from the Helsinki Institute of Technology. He worked at IBM for over thirty years, as a Database Specialist since 1975 (first IMS, then DB2 and other relational DBMSs). He has taught hundreds of database performance courses in 20 different countries, and was the main author of IBM's current global courses for DB2 (for z/OS) performance. He is the author and co-author of several books and articles on database performance. Since 2003 he has been an independent consultant on database performance, and teaches a product-independent index design course.



Mike Leach recently retired from a position at IBM, with 20 years experience teaching database classes at IBM locations in the UK and other countries. In 1990, he and Mr. Leach developed?a?DB2 Application Performance course, which is still taught in IBM Education Centers around the world.