E-raamat: Physical Database Design: The Database Professional's Guide to Exploiting Indexes, Views, Storage, and More

Preface		xv
Organization		xvi
Usage Examples		xvii
Literature Summaries and Bibliography		xviii
Feedback and Errata		xviii
Acknowledgments		xix
Introduction to Physical Database Design		1	(14)
Motivation---The Growth of Data and Increasing Relevance of Physical Database Design		2	(3)
Database Life Cycle		5	(2)
Elements of Physical Design: Indexing, Partitioning, and Clustering		7	(4)
Indexes		8	(1)
Materialized Views		9	(1)
Partitioning and Multidimensional Clustering		10	(1)
Other Methods for Physical Database Design		10	(1)
Why Physical Design Is Hard		11	(1)
Literature Summary		12	(3)
Basic Indexing Methods		15	(16)
B+tree Index		16	(4)
Composite Index Search		20	(5)
Composite Index Approach		24	(1)
Table Scan		24	(1)
Bitmap Indexing		25	(2)
Record Identifiers		27	(1)
Summary		28	(1)
Literature Summary		28	(3)
Query Optimization and Plan Selection		31	(22)
Query Processing and Optimization		32	(1)
Useful Optimization Features in Database Systems		32	(2)
Query Transformation or Rewrite		32	(1)
Query Execution Plan Viewing		33	(1)
Histograms		33	(1)
Query Execution Plan Hints		33	(1)
Optimization Depth		34	(1)
Query Cost Evaluation---An Example		34	(7)
Example Query 3.1		34	(7)
Query Execution Plan Development		41	(2)
Transformation Rules for Query Execution Plans		42	(1)
Query Execution Plan Restructuring Algorithm		42	(1)
Selectivity Factors, Table Size, and Query Cost Estimation		43	(7)
Estimating Selectivity Factor for a Selection Operation or Predicate		43	(2)
Histograms		45	(1)
Estimating the Selectivity Factor for a Join		46	(1)
Example Query 3.2		46	(3)
Example Estimations of Query Execution Plan Table Sizes		49	(1)
Summary		50	(1)
Literature Summary		51	(2)
Selecting Indexes		53	(18)
Indexing Concepts and Terminology		53	(2)
Basic Types of Indexes		54	(1)
Access Methods for Indexes		55	(1)
Indexing Rules of Thumb		55	(3)
Index Selection Decisions		58	(4)
Join Index Selection		62	(7)
Nested-loop Join		62	(3)
Block Nested-loop Join		65	(1)
Indexed Nested-loop Join		65	(1)
Sort-merge Join		66	(1)
Hash Join		67	(2)
Summary		69	(1)
Literature Summary		70	(1)
Selecting Materialized Views		71	(26)
Simple View Materialization		72	(5)
Exploiting Commonality		77	(7)
Exploiting Grouping and Generalization		84	(2)
Resource Considerations		86	(3)
Examples: The Good, the Bad, and the Ugly		89	(3)
Usage Syntax and Examples		92	(3)
Summary		95	(1)
Literature Review		96	(1)
Shared-nothing Partitioning		97	(28)
Understanding Shared-nothing Partitioning		98	(3)
Shared-nothing Architecture		98	(2)
Why Shared Nothing Scales So Well		100	(1)
More Key Concepts and Terms		101	(1)
Hash Partitioning		101	(2)
Pros and Cons of Shared Nothing		103	(3)
Use in OLTP Systems		106	(2)
Design Challenges: Skew and Join Collocation		108	(2)
Data Skew		108	(1)
Collocation		109	(1)
Database Design Tips for Reducing Cross-node Data Shipping		110	(7)
Careful Partitioning		110	(1)
Materialized View Replication and Other Duplication Techniques		111	(4)
The Internode Interconnect		115	(2)
Topology Design		117	(3)
Using Subsets of Nodes		117	(2)
Logical Nodes versus Physical Nodes		119	(1)
Where the Money Goes		120	(1)
Grid Computing		120	(1)
Summary		121	(1)
Literature Summary		122	(3)
Range Partitioning		125	(18)
Range Partitioning Basics		126	(2)
List Partitioning		128	(1)
Essentials of List Partitioning		128	(1)
Composite Range and List Partitioning		128	(1)
Syntax Examples		129	(2)
Administration and Fast Roll-in and Roll-out		131	(3)
Utility Isolation		131	(2)
Roll-in and Roll-out		133	(1)
Increased Addressability		134	(1)
Partition Elimination		135	(3)
Indexing Range Partitioned Data		138	(1)
Range Partitioning and Clustering Indexes		139	(1)
The Full Gestalt: Composite Range and Hash Partitioning with Multidimensional Clustering		139	(3)
Summary		142	(1)
Literature Summary		142	(1)
Multidimensional Clustering		143	(24)
Understanding MDC		144	(7)
Why Clustering Helps So Much		144	(1)
MDC		145	(6)
Syntax for Creating MDC Tables		151	(1)
Performance Benefits of MDC		151	(1)
Not Just Query Performance: Designing for Roll-in and Roll-out		152	(1)
Examples of Queries Benefiting from MDC		153	(4)
Storage Considerations		157	(2)
Designing MDC Tables		159	(6)
Constraining the Storage Expansion Using Coarsification		159	(3)
Monotonicity for MDC Exploitation		162	(1)
Picking the Right Dimensions		163	(2)
Summary		165	(1)
Literature Summary		166	(1)
The Interdependence Problem		167	(10)
Strong and Weak Dependency Analysis		168	(2)
Pain-first Waterfall Strategy		170	(1)
Impact-first Waterfall Strategy		171	(1)
Greedy Algorithm for Change Management		172	(1)
The Popular Strategy (the Chicken Soup Algorithm)		173	(2)
Summary		175	(1)
Literature Summary		175	(2)
Counting and Data Sampling in Physical Design Exploration		177	(20)
Application to Physical Database Design		178	(6)
Counting for Index Design		180	(1)
Counting for Materialized View Design		180	(2)
Counting for Multidimensional Clustering Design		182	(1)
Counting for Shared-nothing Partitioning Design		183	(1)
The Power of Sampling		184	(8)
The Benefits of Sampling with SQL		184	(1)
Sampling for Database Design		185	(4)
Types of Sampling		189	(3)
Repeatability with Sampling		192	(1)
An Obvious Limitation		192	(2)
Summary		194	(1)
Literature Summary		195	(2)
Query Execution Plans and Physical Design		197	(26)
Getting from Query Text to Result Set		198	(3)
What Do Query Execution Plans Look Like?		201	(1)
Nongraphical Explain		201	(4)
Exploring Query Execution Plans to Improve Database Design		205	(6)
Query Execution Plan Indicators for Improved Physical Database Designs		211	(3)
Exploring without Changing the Database		214	(1)
Forcing the Issue When the Query Optimizer Chooses Wrong		215	(5)
Three Essential Strategies		215	(1)
Introduction to Query Hints		216	(3)
Query Hints When the SQL Is Not Available to Modify		219	(1)
Summary		220	(1)
Literature Summary		220	(3)
Automated Physical Database Design		223	(42)
What-if Analysis, Indexes, and Beyond		225	(4)
Automated Design Features from Oracle, DB2, and SQL Server		229	(11)
IBM DB2 Design Advisor		231	(3)
Microsoft SQL Server Database Tuning Advisor		234	(4)
Oracle SQL Access Advisor		238	(2)
Data Sampling for Improved Statistics during Analysis		240	(2)
Scalability and Workload Compression		242	(5)
Design Exploration between Test and Production Systems		247	(1)
Experimental Results from Published Literature		248	(6)
Index Selection		254	(1)
Materialized View Selection		254	(2)
Multidimensional Clustering Selection		256	(2)
Shared-nothing Partitioning		258	(2)
Range Partitioning Design		260	(2)
Summary		262	(1)
Literature Summary		262	(3)
Down to the Metal: Server Resources and Topology		265	(52)
What You Need to Know about CPU Architecture and Trends		266	(5)
CPU Performance		266	(3)
Amdahl's Law for System Speedup with Parallel Processing		269	(2)
Multicore CPUs		271	(1)
Client Server Architectures		271	(2)
Symmetric Multiprocessors and NUMA		273	(2)
Symmetric Multiprocessors and NUMA		273	(1)
Cache Coherence and False Sharing		274	(1)
Server Clusters		275	(1)
A Little about Operating Systems		275	(1)
Storage Systems		276	(3)
Disks, Spindles, and Striping		277	(1)
Storage Area Networks and Network Attached Storage		278	(1)
Making Storage Both Reliable and Fast Using RAID		279	(9)
History of RAID		279	(2)
RAID 0		281	(1)
RAID 1		281	(1)
RAID 2 and RAID 3		282	(2)
RAID 4		284	(1)
RAID 5 and RAID 6		284	(1)
RAID 1+0		285	(1)
RAID 0+1		285	(1)
RAID 10+0 and RAID 5+0		286	(2)
Which RAID Is Right for Your Database Requirements?		288	(1)
Balancing Resources in a Database Server		288	(2)
Strategies for Availability and Recovery		290	(5)
Main Memory and Database Tuning		295	(19)
Memory Tuning by Mere Mortals		295	(3)
Automated Memory Tuning		298	(3)
Cutting Edge: The Latest Strategy in Self-tuning Memory Management		301	(13)
Summary		314	(1)
Literature Summary		314	(3)
Physical Design for Decision Supports, Warehousing, and OLAP		317	(20)
What Is OLAP?		318	(2)
Dimension Hierarchies		320	(1)
Star and Snowflake Schemas		321	(2)
Warehouses and Marts		323	(4)
Scaling Up the System		327	(1)
DSS, Warehousing, and OLAP Design Considerations		328	(1)
Usage Syntax and Examples for Major Database Servers		329	(4)
Oracle		330	(1)
Microsoft's Analysis Services		331	(2)
Summary		333	(1)
Literature Summary		334	(3)
Denormalization		337	(20)
Basics of Normalization		338	(4)
Common Types of Denormalization		342	(4)
Two Entities in a One-to-One Relationship		342	(1)
Two Entities in a One-to-many Relationship		343	(3)
Table Denormalization Strategy		346	(1)
Example of Denormalization		347	(7)
Requirements Specification		347	(2)
Logical Design		349	(1)
Schema Refinement Using Denormalization		350	(4)
Summary		354	(1)
Literature Summary		354	(3)
Distributed Data Allocation		357	(14)
Introduction		358	(2)
Distributed Database Allocation		360	(2)
Replicated Data Allocation---``All-beneficial Sites'' Method		362	(5)
Example		362	(5)
Progressive Table Allocation Method		367	(1)
Summary		368	(1)
Literature Summary		369	(2)
Appendix A A Simple Performance Model for Databases		371	(4)
I/O Time Cost---Individual Block Access		371	(1)
I/O Time Cost---Table Scans and Sorts		372	(1)
Network Time Delays		372	(2)
CPU Time Delays		374	(1)
Appendix B Technical Comparison of DB2 HADR with Oracle Data Guard for Database Disaster Recovery		375	(4)
Standby Remains ``Hot'' during Failover		376	(1)
Subminute Failover		377	(1)
Geographically Separated		377	(1)
Support for Multiple Standby Servers		377	(1)
Support for Read on the Standby Server		377	(1)
Primary Can Be Easily Reintegrated after Failover		378	(1)
Glossary		379	(12)
Bibliography		391	(20)
Index		411	(16)
About the Authors		427

Sam Lightstone is a Senior Technical Staff Member and Development Manager with IBMs DB2 product development team. His work includes numerous topics in autonomic computing and relational database management systems. He is cofounder and leader of DB2s autonomic computing R&D effort. He is Chair of the IEEE Data Engineering Workgroup on Self Managing Database Systems and a member of the IEEE Computer Society Task Force on Autonomous and Autonomic Computing. In 2003 he was elected to the Canadian Technical Excellence Council, the Canadian affiliate of the IBM Academy of Technology. He is an IBM Master Inventor with over 25 patents and patents pending; he has published widely on autonomic computing for relational database systems. He has been with IBM since 1991. Toby J. Teorey is a professor in the Electrical Engineering and Computer Science Department at the University of Michigan, Ann Arbor. He received his B.S. and M.S. degrees in electrical engineering from the University of Arizona, Tucson, and a Ph.D. in computer sciences from the University of Wisconsin, Madison. He was general chair of the 1981 ACM SIGMOD Conference and program chair for the 1991 Entity-Relationship Conference. Professor Teoreys current research focuses on database design and data warehousing, OLAP, advanced database systems, and performance of computer networks. He is a member of the ACM and the IEEE Computer Society. Tom Nadeau is the founder of Aladdin Software (aladdinsoftware.com) and works in the area of data and text mining. He received his B.S. degree in computer science and M.S. and Ph.D. degrees in electrical engineering and computer science from the University of Michigan, Ann Arbor. His technical interests include data warehousing, OLAP, data mining and machine learning. He won the best paper award at the 2001 IBM CASCON Conference.