Customer Reviews

Physical Database Design: the database professional's guide to exploiting indexes, views, storage, and more (The Morgan Kaufmann Series in Data Management Systems)

5 star:		(0)
4 star:		(3)
3 star:		(0)
2 star:		(2)
1 star:		(0)

 

 

This book fills a gap in the marketplace that has needed to be filled for some time now. Oh, there are a lot of good books on each individual DBMS and there are some very good books on logical database design and data modeling. But until now, there has not been a good book that provides a comprehensive treatment of the physical side of database design from a DBMS agnostic perspective.

Actually, the last book that I can recall to tackle the subject well was Handbook of Relational Database Design by Fleming and von Halle (Addison-Wesley, ISBN 0-201-11434-8); but that book was published way back in 1989 and it is a bit long in the tooth.

Physical Database Design is co-authored by three database design experts: Sam Lightstone, Toby Teorey, and Tom Nadeau. Lightstone is a Senior Technical Staff Member and Development Manager with IBM's DB2 product development team. Teorey is a professor emeritus in the Electrical Engineering and Computer Science Department and Director of Academic Programs in the College of Engineering at the University of Michigan, Ann Arbor. And Nadeau is the founder of Aladdin Software and works in the area of data and text mining. So it is no surprise that the book has a solid technical foundation.

The book offers comprehensive coverage of how to design the physical structures and environment for the most popular database management systems. After reading Physical Database Design you will come away with a better understanding of how the choices you make during physical design affect the performance of your systems. The specific examples, guidelines, and best and worst practices included by the authors are instructive and enlightening, as well as being helpful across the spectrum of popular DBMSs. Examples and code illustrating the major concepts of physical database design are depicted for IBM DB2, Oracle, Microsoft SQL Server, and even sometimes for Informix. It might have been useful to also cover MySQL, but that is not a major criticism as the three most popular DBMSs are nicely addressed by the authors.

So what types of issues are covered within the book? Well, of course, it offers guidance on how to create the objects needed to support a database; tables, indexes, etc. The book also deals with the nuances of setting up more complicated database structures such as partitioning and clustering. And performance issues are covered in some depth as well; in fact, two of the first four chapters are on indexing methods and issues.

One of the more interesting sections of the book is the chapter on automated physical database design. Over the past few years the major DBMS vendors have been adding more autonomic features to their products and the authors address the autonomic physical design aspects quite nicely. These features include the IBM DB2 Design Advisor, the Microsoft SQL Server Database Tuning Advisor, and Oracle's SQL Access Advisor.

If you are interested in how database systems interact with hardware the chapter titled "Down to the Metal: Server Resources and Topology" will be instructive. It discusses CPU architectures, client/server architectures, SMP and NUMA, server clusters, storage and RAID, resource balancing, and availability issues. This is an area where many DBAs lack in-depth knowledge, so this chapter should be welcome reading.

Another very nice feature of the book is the "Tips and Insights for Database Professionals" that end each chapter. These highlighted sections cover the primary ideas that were presented in that chapter in the form of tips you can follow to improve your database design.

As good as this book is though, it is not perfect. Unfortunately, the authors make the cardinal mistake of sometimes using the words "always" and "never." It is almost never a good idea to use "always" or "never" when talking about database design and performance. For example, the authors offer the following advice on page 28: "Indexing should always be used for access to a small number of rows for queries." Now this advice may sound reasonable, and it would be if you just remove the word "always." For example, if you only have a small total number of rows in a table then just scanning the table is likely to be more efficient than going through an index because that would add I/O.

Putting small quibbles like that aside, Physical Database Design is a well-written and researched book that should prove useful to any DBA or developer looking to improve their skills in creating efficient and effective relational database implementation. Consider adding it to your library.

This is NOT a book for people looking to learn SQL, or some other dialect of a database. It is a data centric design book on the low level parts of those database engines you currently use.

Did you ever wonder WHY heap files and B-Tree files are used in databases? This is the book to give you the why behind a lot of the lower level design decisions in databases today.

I enjoyed the why discussion of almost all of the topics, but it left me feeling a little light on the implementation. There is not much in the way of practical discussion in the book. While a discussion about index types is very well thought out it never completes the thought from an implementation standpoint. I was actually left with a lot more questions to low level implementation than I started. When to use one of these algorithms is totally left as an excercise to the reader. And in many cases the actual algorithm itself is also left to you to research. I guess they are outside the scope of the book, but I would have enjoyed it so much more with some simple pseudocode or a sample project concept to put them to use.

The book is a great reference to get you thinking about the lowest level of database implementation and make you really think about the way in which SQL Server, DB2, etc all work. But you are not left with a lot of substance on why one implementation would be better than the other for your specific application or problem domain.

This book starts with an overview of where in the overall database life cycle physical database design stands.

Basic indexing methods such as the all too common B+tree, hash table and bitmap are addressed, differences and benefits explained. Index selection methods and trade-offs are assessed in a sensible way. A useful set of rules of thumb on indexing is provided as well.

Next come partitioning and clustering techniques such as shared-nothing and hash range partitioning and MDC (Multi Dimensional Clustering). Pros and cons are provided and explained. Sound examples that illustrate the usage combinations of indexing, partitioning and clustering techniques are given.

Query optimization, plan selection and execution aspects are addressed with focus on where the indexing, partitioning and clustering techniques previously discussed fit in and on how physical design can be improved by selecting the right plan.

There is a chapter on automated database physical design that, although it does not focus much on a particular product, gives an overview of the way IBM DB2 Design Advisor, MS SQL Server Database Tuning Advisor and Oracle SQL Access Advisor handle tunings and optimizations.

Server topology, hardware aspects like CPU, memory, storage systems, performance aspects and their impacts are addressed as well.
The last two chapters are, respectively, about performance improvements through denormalization and specifics of distributed database allocation.

Here we have a useful book that covers the main aspects of physical database design and thoroughly discusses them. The book content is accessible to someone having - at least - basic database knowledge, although I found not all parts of it are easy to grasp. If you get this book, depending on your database proficiency level, you may feel like you need to read parts of it more than a couple of times to get what the authors meant to say. This is not a beginner's book: Some prerequisite knowledge on Data Modeling and on SQL is required. If not the case, better to start from there.

What I liked most in this book is the "Tips and Insights for Database Professionals" section that is located at the end of each chapter and that acts as a sort of "cookbook" summarizing the outcomes/conclusions of the chapter it relates to. Once the core of the book grasped, you can keep going by the "Tips and Insights..." only.

By the reading this book alone, the reader cannot expect to become an expert in physical database design, but there is valuable information in it that will allow the designer to be better aware of the automatic/by default performance optimization options of the actual database product he/she'll probably be using most of the time.

I identified 18 typing errors in the entire book, but nothing significant to mention.
Taking into account the above, I can easily recommend this book.

As other reviewer I bought the book because of recommendation. I must admit though, I haven't read it through, just spotted few chapters. While Table of Contents looks impressive actual filling is not of highest quality. For example introduction paragraph on Indexes is so vague and misleading that were I still a student knowing nothing about RDBMS I would understand nothing. It introduces UNIQUE indexes and then basically declares it is the same as ORDERED index. Description of non-unique indexes is cryptic. Statement on performance of multi-column indexes is not correct at best. I'm not saying that main chapters on indexes are bad, but this introduction is just a disaster.

RAID. Useless chapter, how you can pretend you are explaining RAID, and giving advice on what to use for RDBMS without providing comparison of protection level, overhead, R/W performance of different configurations?! The book is called "Physical database design" let me remind you.

I'm sure there should be something valuable in this book, I just haven't got to it yet.

I purchased this book because I saw a recommendation from Jim Gray on the back cover. While the book's content is reasonable, the number of typos in the book is nothing short of appalling. Readers like me will be put off by the sloppy editing (or perhaps the absence of any editing whatsoever).