The first commercially available relational database was developed by Larry Ellison’s Relational Software, Inc. (now Oracle Corporation) in 1977. Now, more than 25 years later, relational databases still rule the database marketplace. Except for a few legacy systems still using older databases (hierarchical and network) and a few specialized applications that use the newer databases (object-oriented, multimedia, spatial, deductive, geographic, and so on), the majority of databases in use today are relational.

During the nascent years, relational databases had many shortcomings, including performance problems, but as they matured most of the drawbacks were overcome, making them the most powerful and easy-to-use database management systems. The phenomenal advancements in hardware and the exponential increase in processing power eliminated many of the factors that hindered the performance of relational databases. But with the improvements in hardware and processing power, the volume of data handled by the databases also increased exponentially. Thus, today’s gigahertz, terabyte, and petaflop computing scene needs new strategies for improving database performance.

To access and manipulate the information in a relational database, we use the structured query language (SQL). SQL is set up so that the programmer can specify what data is needed, but doesn’t need to specify how to retrieve it--a task taken care of by the SQL optimizer. The optimizer is the brain of a relational database, and it decides how to find the required information quickly using the minimum amount of resources. The optimizer scans the indexes and tables to find the best access strategy, thereby making index design, table design, and SQL coding crucial factors from the performance point of view.

This book is about optimizers, SQL statements, indexes, and other factors that affect the performance of a relational database. It contains 16 chapters, a reference section, and a glossary, in addition to the preface and index. In the preface, the authors explain the importance of database performance, how the old performance-tuning strategies have changed, the contents of the book, who should read it, and so on.

The first three chapters serve as an introduction. Chapter 1 dispels the common myths and misconceptions about database performance, and gives an overview of systematic index design. Chapter 2 familiarizes the readers with concepts like table and index design, file organization, data access, indexing, and hashing. The third chapter explains how the optimizer processes the SQL statements and various related concepts.

Chapter 4 covers the various factors that affect the performance of SELECT statements, and guides the reader, using appropriate examples, in creating the best possible indexes for the SELECT statement. The fifth chapter, “Proactive Index Design,” explains two techniques--basic question (BQ) and quick upper-bound estimate (QUBE)--that will help in determining whether the index design needs to be improved to provide acceptable SQL performance. Chapter 6 provides an overview of the various factors that affect the index design.

Chapter 7 deals with the EXPLAIN command and how it should be used for designing indexes. This chapter also explains how to use LRT-level and call-level exception monitoring, bubble charts, spike reports, and other mechanisms to identify problem areas. Chapter 8 gives an overview of the different join operations, and discusses the various issues that affect join performance and how to avoid them. The ninth chapter deals with star joins, fact and dimension tables, and how to design indexes for them. Chapter 10 is about multiple index access, and discusses when index intersections and unions are useful and how they affect index design.

Chapter 11 describes the physical structure of indexes, index access mechanisms, and index reorganization. This chapter explains in detail why, how, and when the indexes should be reorganized. Chapter 12 discusses the restrictions imposed on indexes by relational database management systems, while chapter 13 deals with the various options provided by them.

Chapter 14 explains why the optimizer does not always choose the best strategy, and how one can help the optimizer select the best alternative. Chapter 15 looks at the various factors that affect the cost estimations and basic assumptions while using the QUBE formula. It also discusses how to estimate the central processing unit (CPU) time using the default CPU coefficients. Chapter 16 gives an overview of computer-assisted index design, and provides a nine-step process for designing excellent indexes.

The reference section contains only nine entries, as most of the ideas and techniques used in the book are developed by the authors. The glossary is excellent, and provides clear explanations of the various terms used in the book.

This book is not like the hundreds of generic database books on the market. It addresses a very specific area--relational database performance improvement. There are hundreds of factors that affect database performance, but the authors have wisely concentrated on the few factors that matter most, producing a handy but invaluable reference. The book concentrates on index design, SQL statements, and the SQL optimizer, and discusses the various issues that affect database performance and various methods to improve it. It explains why the old performance-tuning rules and tactics are no longer true, and provides new guidelines and strategies.

One of the most useful features of this book is its vendor independence. The guidelines and advice given in the book and the concepts explained are equally applicable to all relational database management systems. Another feature that adds value to the book is its use of examples, case studies, and solved problems. The authors provide relevant examples and illustrations, which go a long way in improving the readability of the book.

I recommend this book to all those who have anything to do with database performance. It is a must-read for all database administrators, database designers, performance-tuning specialists, and application programmers who use SQL in their programs.