Two Excellent Index Related Blog Posts May 25, 2009Posted by Richard Foote in Oracle Indexes.
Before I sit down and write something new, thought I might quickly mention two excellent recent index related blog posts,written by two very clever chaps.
The first is by Jonathan Lewis who discusses in a post called Index Size how one might determine which indexes to perhaps consider for an index rebuild by using the dbms_space.create index_cost procedure. By comparing the difference between the current size of the index and the potential size of a newly created index as calculated by the procedure, one can check to see if the difference in size might make a rebuild a worthwhile proposition. Jonathan then discusses a number of deficiencies and little traps with this approach.
The second is by Alberto Dell’Era who discusses in a post called Order of keys inside index blocks exactly how Oracle orders and stores the index keys within an index block. I’ve exchanged a number of emails with Alberto on this subject and he does an excellent job of detailing exactly how Oracle orders the index row directories and the advantages of Oracle doing so.
Both are highly recommended and well worth a read if you’re interested in learning more on Oracle indexes.