Interesting Index “Facts” – What is Wrong with these Quotes?

22 02 2012

February 22, 2012

There are some amazing (or just plain interesting) facts to be found about how indexes work in Oracle Database.  One Internet site in particular that has been helpful in my understanding of Oracle indexes is Richard Foote’s blog.  I occasionally see information on the Internet, in the Oracle documentation, in Metalink (My Oracle Support), and in books that conflict with one another – just how does one determine what information is correct, and what information is incorrect? 

If you paid money to learn something, should you just assume that what you are learning is correct?  What if test cases are included, should you just assume that the test case is showing what the document claims that the test case shows?  What if, you never ask “What if?” … what if, you do not know when to ask “What if?”

As I mentioned in a previous article, I am in the process of reading and reviewing the book “Oracle Database 11gR2 Performance Tuning Cookbook“, and I seem to be spending quite a bit of time asking What if … or why does this statement not agree with what I believe to be true?  For this blog article, I have pulled seven quotes from the book that will be mentioned in my review of the book – I thought that I would give the readers of this blog an opportunity to ask, “What if”.  As always, I attempted to keep the length of the quotes to a minimum – just long enough so that the context (and meaning) of the quote is not lost.

#1 Page 88:

Faster scans: tables and indexes that require full scans can see faster performance when placed in a large block size.”

#2 Page 119:

“We create the IX3_CUSTOMERS index to show how we can create a descending index, that is, an index which stores the data for one or more fields in descending order…

We can also create a function-based descending index.”

#3 Page 119:

“… this test allows us to dispel a myth. Oracle uses the indexes even if the leading columns are not referenced in the WHERE predicate of the query.  We can see that in such a case, the operation will be an INDEX FAST FULL SCAN.  Conversely, when we reference the leading column of the index, we will end up in an INDEX RANGE SCAN.

#4 Page 120:

“The last type [descending indexes] is useful when we want to use queries in which we want the data sorted in a mixed ascending/descending way on the fields indexed. If we use a regular index to access the data, Oracle is unable to do the sort in a mixed way, in a query like this…  ORDER BY FIELD1 DESC, FIELD2

#5 Page 127:

“However, when we have a table on which there are many INSERTs and DELETEs, we could schedule an index rebuild, because when deleting an index entry, the space is not freed in the index leaf, but just marked as deleted. If we have massive DELETE and INSERT operations, we could have a skewed index structure, which could slow performance due to intra-block fragmentation.” 

#6 Page 127:

“If the value for DEL_LF_ROWS/LF_ROWS is greater than 2, or LF_ROWS is lower than LF_BLKS, or HEIGHT is 4 then the index should be rebuilt.”

#7 Page 139:

“When rows are frequently inserted, deleted, and updated, there is a performance bottleneck if we use a bitmap index. When the index is updated, all the bitmap segments are locked.”

What, if anything, is wrong (and/or right) with the above quotes from the book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.