14 of 15 people found the following review helpful
Performance by indexing,
This review is from: Relational Database Index Design and the Optimizers (Hardcover)Before I start commenting on this book, I should point out that I have been using, running, designing, or trouble-shooting Oracle database systems for most of the last 20 years - but the authors have a background in DB2.
Having made said that, the difference in RDBMS really doesn't matter. If you are responsible for making databases perform well you should read this book.
It tells you why you need to create indexes - and the answer is a little more subtle than just "to make things go faster"; it tells you how to decide on the columns to include in your indexes; it tells you how to choose the best order for those columns; and it describes the compromises you make between a reasonable set of pretty good indexes and the complete set of ideal indexes
Experienced DBAs may read this book and decide that it's not telling them anything new. Even if you fall into this group, don't use that as an excuse for putting the book to one side. The clarity of explanation, the rational justification, and the numerical approximation methods supplied by this book should give you a better insight - hence greater effectiveness - into what may at present be an intuitive approach to deciding on indexing strategies.
The book gives you useful models for working out the benefit of adding, or modifying, an index in terms of how much work you do as you walk through the index and visit the table. It gives you a simple system for classifying an index with one, two, or three stars, and then offers an insight into when you may or may not,want to create the ideal index for a query.
Specifically for the Oracle practitioner, I found a few minor details that required a slight mental adjustment: the authors tend to talk about 4Kb block sizes with relatively large index entries whereas many Oracle systems tend to use 8Kb block sizes with relatively small index entries; there were points where the experienced Oracle DBA would want to think about allowing for the background workload due to UNDO and REDO; there were details where I could see some difference between DB2 and Oracle technology; and the authors also have a section discussing when to rebuild indexes (an activity generally viewed as anathema in the Oracle world).
Given the common Oracle view about index rebuilding - and my background - I feel it is important to say that the authors' arguments are perfectly sound, and I would be quite happy to follow their guidelines; but I would only expect the argument to apply to a very small percentage of the indexes in a typical Oracle (OLTP) system. This is because there was one detail that I thought needed to be raised more explicitly - despite being implicit in a large fraction of the writing - the authors' were talking about small blocks with large index entries for indexes that have been engineered so that critical queries did most of their work IN THE INDEX, making "one more random block read" a significant fraction of the total workload.
These are minor details, though, that have no impact on the general principles; at most they move the boundary line at which you decide whether the cost of any structural change is worth the benefit offered by that change.
In summary - any DBA should read this book if they need to get better performance from a badly indexed database.
(1 customer review)