Physical Database Design: the database professional's guide to exploiting indexes, views, storage, and more
Regular price
23.000 KD
inc. VAT
Couldn't load pickup availability
Table of contents
- Cover
- Contentsv
- Prefacexv
- Chapter 1. Introduction to Physical Database Design1
- 1.1 Motivation„The Growth of Data and Increasing Relevance of Physical Database Design2
- 1.2 Database Life Cycle5
- 1.3 Elements of Physical Design: Indexing, Partitioning, and Clustering7
- 1.4 Why Physical Design Is Hard11
- 1.5 Literature Summary12
- Chapter 2. Basic Indexing Methods15
- 2.1 B+tree Index16
- 2.2 Composite Index Search20
- 2.3 Bitmap Indexing25
- 2.4 Record Identifiers27
- 2.5 Summary28
- 2.6 Literature Summary28
- Chapter 3. Query Optimization and Plan Selection31
- 3.1 Query Processing and Optimization32
- 3.2 Useful Optimization Features in Database Systems32
- 3.3 Query Cost Evaluation„An Example34
- 3.4 Query Execution Plan Development41
- 3.5 Selectivity Factors, Table Size, and Query Cost Estimation43
- 3.6 Summary50
- 3.7 Literature Summary51
- Chapter 4. Selecting Indexes53
- 4.1 Indexing Concepts and Terminology53
- 4.2 Indexing Rules of Thumb55
- 4.3 Index Selection Decisions58
- 4.4 Join Index Selection62
- 4.5 Summary69
- 4.6 Literature Summary70
- Chapter 5. Selecting Materialized Views71
- 5.1 Simple View Materialization72
- 5.2 Exploiting Commonality77
- 5.3 Exploiting Grouping and Generalization84
- 5.4 Resource Considerations86
- 5.5 Examples: The Good, the Bad, and the Ugly89
- 5.6 Usage Syntax and Examples92
- 5.7 Summary95
- 5.8 Literature Review96
- Chapter 6. Shared-nothing Partitioning97
- 6.1 Understanding Shared-nothing Partitioning98
- 6.2 More Key Concepts and Terms101
- 6.3 Hash Partitioning101
- 6.4 Pros and Cons of Shared Nothing103
- 6.5 Use in OLTP Systems106
- 6.6 Design Challenges: Skew and Join Collocation108
- 6.7 Database Design Tips for Reducing Cross-node Data Shipping110
- 6.8 Topology Design117
- 6.9 Where the Money Goes120
- 6.10 Grid Computing120
- 6.11 Summary122
- 6.12 Literature Summary122
- Chapter 7. Range Partitioning125
- 7.1 Range Partitioning Basics126
- 7.2 List Partitioning128
- 7.3 Syntax Examples129
- 7.4 Administration and Fast Roll-in and Roll-out131
- 7.5 Increased Addressability134
- 7.6 Partition Elimination135
- 7.7 Indexing Range Partitioned Data138
- 7.8 Range Partitioning and Clustering Indexes139
- 7.9 The Full Gestalt: Composite Range and Hash Partitioning with Multidimensional Clustering139
- 7.10 Summary142
- 7.11 Literature Summary142
- Chapter 8. Multidimensional Clustering143
- 8.1 Understanding MDC144
- 8.2 Performance Benefits of MDC151
- 8.3 Not Just Query Performance: Designing for Roll-in and Roll-out152
- 8.4 Examples of Queries Benefiting from MDC153
- 8.5 Storage Considerations157
- 8.6 Designing MDC Tables159
- 8.7 Summary165
- 8.8 Literature Summary166
- Chapter 9. The Interdependence Problem167
- 9.1 Strong and Weak Dependency Analysis168
- 9.2 Pain-first Waterfall Strategy170
- 9.3 Impact-first Waterfall Strategy171
- 9.4 Greedy Algorithm for Change Management172
- 9.5 The Popular Strategy (the Chicken Soup Algorithm)173
- 9.6 Summary175
- 9.7 Literature Summary175
- Chapter 10. Counting and Data Sampling in Physical Design Exploration177
- 10.1 Application to Physical Database Design178
- 10.2 The Power of Sampling184
- 10.3 An Obvious Limitation192
- 10.4 Summary194
- 10.5 Literature Summary195
- Chapter 11. Query Execution Plans and Physical Design197
- 11.1 Getting from Query Text to Result Set198
- 11.2 What Do Query Execution Plans Look Like?201
- 11.3 Nongraphical Explain201
- 11.4 Exploring Query Execution Plans to Improve Database Design205
- 11.5 Query Execution Plan Indicators for Improved Physical Database Designs211
- 11.6 Exploring without Changing the Database214
- 11.7 Forcing the Issue When the Query Optimizer Chooses Wrong215
- 11.8 Summary220
- 11.9 Literature Summary220
- Chapter 12. Automated Physical Database Design223
- 12.1 What-if Analysis, Indexes, and Beyond225
- 12.2 Automated Design Features from Oracle, DB2, and SQL Server229
- 12.3 Data Sampling for Improved Statistics during Analysis240
- 12.4 Scalability and Workload Compression242
- 12.5 Design Exploration between Testand Production Systems247
- 12.6 Experimental Results from Published Literature248
- 12.7 Index Selection254
- 12.8 Materialized View Selection254
- 12.9 Multidimensional Clustering Selection256
- 12.10 Shared-nothing Partitioning258
- 12.11 Range Partitioning Design260
- 12.12 Summary262
- 12.13 Literature Summary262
- Chapter 13. Down to the Metal: Server Resources and Topology265
- 13.1 What You Need to Know about CPU Architecture and Trends266
- 13.2 Client Server Architectures271
- 13.3 Symmetric Multiprocessors and NUMA273
- 13.4 Server Clusters275
- 13.5 A Little about Operating Systems275
- 13.6 Storage Systems276
- 13.7 Making Storage Both Reliable and Fast Using RAID279
- 13.8 Balancing Resources in a Database Server288
- 13.9 Strategies for Availability and Recovery290
- 13.10 Main Memory and Database Tuning295
- 13.11 Summary314
- 13.12 Literature Summary314
- Chapter 14. Physical Design for Decision Support, Warehousing, and OLAP317
- 14.1 What Is OLAP?318
- 14.2 Dimension Hierarchies320
- 14.3 Star and Snowflake Schemas321
- 14.4 Warehouses and Marts323
- 14.5 Scaling Up the System327
- 14.6 DSS, Warehousing, and OLAP Design Considerations328
- 14.7 Usage Syntax and Examples for MajorDatabase Servers329
- 14.8 Summary333
- 14.9 Literature Summary334
- Chapter 15. Denormalization337
- 15.1 Basics of Normalization338
- 15.2 Common Types of Denormalization342
- 15.3 Table Denormalization Strategy346
- 15.4 Example of Denormalization347
- 15.5 Summary354
- 15.6 Literature Summary354
- Chapter 16. Distributed Data Allocation357
- 16.1 Introduction358
- 16.2 Distributed Database Allocation360
- 16.3 Replicated Data Allocation„All-beneficial SitesŽ Method362
- 16.4 Progressive Table Allocation Method367
- 16.5 Summary368
- 16.6 Literature Summary369
- Appendix A. Simple A Performance Model for Databases371
- A.1 I/O Time Cost„Individual Block Access371
- A.2 I/O Time Cost„Table Scans and Sorts372
- A.3 Network Time Delays372
- A.4 CPU Time Delays374
- Appendix B. Technical Comparison of DB2 HADR with Oracle Data Guard for Database Disaster Recovery375
- B.1 Standby Remains HotŽ during Failover376
- B.2 Subminute Failover377
- B.3 Geographically Separated377
- B.4 Support for Multiple Standby Servers377
- B.5 Support for Read on the Standby Server377
- B.6 Primary Can Be Easily Reintegrated after Failover378
- Glossary379
- Bibliography391
- Index411
- About the Authors427
The rapidly increasing volume of information contained in relational databases places a strain on databases, performance, and maintainability: DBAs are under greater pressure than ever to optimize database structure for system performance and administration.
Physical Database Design discusses the concept of how physical structures of databases affect performance, including specific examples, guidelines, and best and worst practices for a variety of DBMSs and configurations. Something as simple as improving the table index design has a profound impact on performance. Every form of relational database, such as Online Transaction Processing (OLTP), Enterprise Resource Management (ERP), Data Mining (DM), or Management Resource Planning (MRP), can be improved using the methods provided in the book.
· The first complete treatment on physical database design, written by the authors of the seminal, Database Modeling and Design: Logical Design, 4th edition.
· Includes an introduction to the major concepts of physical database design as well as detailed examples, using methodologies and tools most popular for relational databases today: Oracle, DB2 (IBM), and SQL Server (Microsoft).
· Focuses on physical database design for exploiting B+tree indexing, clustered indexes, multidimensional clustering (MDC), range partitioning, shared nothing partitioning, shared disk data placement, materialized views, bitmap indexes, automated design tools, and more!
Physical Database Design discusses the concept of how physical structures of databases affect performance, including specific examples, guidelines, and best and worst practices for a variety of DBMSs and configurations. Something as simple as improving the table index design has a profound impact on performance. Every form of relational database, such as Online Transaction Processing (OLTP), Enterprise Resource Management (ERP), Data Mining (DM), or Management Resource Planning (MRP), can be improved using the methods provided in the book.
· The first complete treatment on physical database design, written by the authors of the seminal, Database Modeling and Design: Logical Design, 4th edition.
· Includes an introduction to the major concepts of physical database design as well as detailed examples, using methodologies and tools most popular for relational databases today: Oracle, DB2 (IBM), and SQL Server (Microsoft).
· Focuses on physical database design for exploiting B+tree indexing, clustered indexes, multidimensional clustering (MDC), range partitioning, shared nothing partitioning, shared disk data placement, materialized views, bitmap indexes, automated design tools, and more!