December 11, 2010
(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)
In the interest of interesting quotes, a quote that I found in the “Oracle Tuning the Definitive Reference Second Edition” book on page 828:
“When the index can no longer split because the owner block is full, Oracle will spawn a whole new index level, keeping the index tree in perfect logical and phyical balance. Deletes are a different story. Physically, Oracle indexes are always balanced because empty blocks stay inside the tree structure after a massive delete. Logically, Oracle indexes are not self-balancing because Oracle does not remove the dead blocks as they become empty. Figure 16.9 shows an Oracle index before a massive delete…”
What, if anything, is wrong with the above quote? Please keep in mind that the focus of this blog is on the technical content, and learning from that technical content. Please stay positive in your responses (before answering, first take a look at page 727 to see if these two sections of the book are related – note that Richard Foote’s PDF file listed below seems to address page 727 of this book).
—————–
While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.
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.
Other pages found during a Google search of the phrase:
- http://www.remote-dba.net/oracle_10g_tuning/t_oracle_index_maintenance.htm
- dba-oracle.com/oracle_tips_intelligent_10g_DBA_maintenance.htm
- The previous edition of the book
- http://forums.oracle.com/forums/thread.jspa?threadID=587369
- http://richardfoote.wordpress.com/2007/12/12/why-are-there-so-many-oracle-related-myths-the-inconvenient-truth/ (excellent article, includes a link to Richard Foote’s Index Internals – Rebuilding the Truth PDF)
- http://richardfoote.wordpress.com/2008/04/03/so-when-does-an-oracle-b-tree-index-increase-in-height-almost-grown/
- http://www.fullinterview.com/allpaperpresentations/oracle/Database%20Design%20A%20Guided%20Approach.doc (interesting document, or just repeating sections of this book?)
- http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:678824574412 (may not be closely related)
“When the index can no longer split because the owner block is full. Oracle will spawn a whole new index level, keeping the index tree in perfect logical and phyical balance. ”
owner block? what is it? Indexes do not split. Leaf blocks split, branch blocks split, root block splits and in fact they split when they *are* too full and no other entry will fit into it. Index level increments only when root block splits – when it has too many child branch blocks and new entry for next branch block does not fit into it.
B*tree structure is physically balanced by definition. I don’t know what exactly is meant by “logical balance”. If logical balance = entries in leaf blocks are uniformly distributed, than it’s not true.. For example, when we have index on monotonously incremented value (sequence.nextval, sysdate), than we are inserting into most right leaf and 90-10 splits happen (99-1 split would by a better name). So when 90-10 split happen then most right leaf has almost empty (only one entry is there) and therefore is index not “logically” balanced.
Richard Foote’s blog is a great reference (i.e http://richardfoote.wordpress.com/?s=split&searchbutton=go!)
“Deletes are a different story. Physically, Oracle indexes are always balanced because empty blocks stay inside the tree structure after a massive delete. Logically, Oracle indexes are not self-balancing because Oracle does not remove the dead blocks as they become empty. Figure 16.9 shows an Oracle index before a massive delete…”
It’s not true that completely empty blocks are not removed from index structure. They are removed and recycled. This is valid for leaf blocks and also for branch blocks, but they has too be completely empty. And empty space is not dead-wood. It can be reused (but not by the same transaction)
Again you can search in Richard Foote’s blog for more info
http://richardfoote.wordpress.com/?s=delete+dead+wood&searchbutton=go!
Radoslav,
Impressive answer – much better than what I had in mind.
When I first read in your comment “(99-1 split would by a better name)” – I initially thought that it was an interesting statement, but did not match my previous knowledge. So, I did a little bit of research (just as I have been doing for the contents of this book) and determined that I had forgotten that a 90-10 split may not (or likely is not) an indication that 10% of the index entries from the leaf block end up in a new index leaf block during an index leaf block split (assuming that a 50-50 split did not take place). I located the following links that helped to refresh my memory:
http://www.freelists.org/post/oracle-l/Index-rebuilding,22
http://forums.oracle.com/forums/thread.jspa?threadID=581038
Regarding the “logical balance”, I think that I nearly determined what the book author is trying to communicate by visiting the first link in this article under the heading “Other pages found during a Google search of the phrase”. I will try to use ASCII art to demonstrate what the author means by physically balanced and logically balanced:
Logically and physically balanced (before the massive delete):
Physically balanced but not logically balanced (after the delete, imagine that the [F] blocks are not present) after the massive delete:
In the above ASCII art, notice that leaf blocks are pictured at the same level as branch blocks. The above ASCII art descriptions definitely do not describe Oracle index behavior, which again brings us back to page 727 of the book.
“In the above ASCII art, notice that leaf blocks are pictured at the same level as branch blocks.”
B*tree can’t look like that. All leaf blocks has the same level – same distance from the root.
I’ve done some tests..
For me, it is interesting that B*Tree index can look like this 🙂
So yes, sometimes the rebuild could be beneficial, you could save some block reads, but this is an extreme scenario..
See Richard Foote blog for more info when may be beneficial to use coalesce, shrink or rebuild.
http://richardfoote.wordpress.com/2008/02/08/index-rebuild-vs-coalesce-vs-shrink-space-pigs-3-different-ones/
Radoslav,
Very nice demonstration, and it gives the readers of this blog article something interesting to experiment with and investigate. I had to visit this blog article to refresh my memory of the meaning of nrow and rrow in a treedump:
http://richardfoote.wordpress.com/2010/02/08/index-block-dumps-and-treedumps-part-i-knock-on-wood/
Your treedump example can be used as evidence that this type of structure, as found in the book, does not represent Oracle B*tree index behavior:
Thank you for adding value to this blog article with a helpful test case.
Radoslav,
I have executed your script on 11.2.0.1 with an ASSM tablespace, but without adding the additional 500,000 rows to the table. I did not see the odd tree dump behavior (leaf: 0x1000d0c 16780556 (6: nrow: 479 rrow: 0)) in my test execution of the test script. I have seen off behavior in block dumps before if the changed block was not flushed to disk.
My results:
From the trace file:
From the trace file:
From the trace file: