RE: indexes
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "gajav@xxxxxxxxx" <gajav@xxxxxxxxx>, "oratune@xxxxxxxxx" <oratune@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 17 Jan 2011 17:08:38 -0700
I second the recommendation below. Here is my understanding as it applies to
the specific case you have described:
The blocks with deleted rows will be reused only if ALL rows have been deleted
from them. If you delete some, but not all rows, then you can end up with the
special case that Richard Foote describes as “monotonically increasing with
sparse deletes” and Tom Kyte refers to as a “sweeper” where you do end up with
a lot of nearly empty blocks that won’t be reused and can definitely benefit
from a rebuild or coalesce.
David said it’s not true that data is always inserted on the right side, but I
believe it is actually true if your index is on a column with a constantly
increasing value such as a sequence or current date as you have described.
Yes, a b-tree index is always “balanced” in terms of the number of branches
from any leaf to the root node, but it may not be balanced in terms of the
number of rows per block on the left side vs. the right side. I think this is
where a lot of the confusion comes from on this topic – in how one defines the
term “balanced” in this context.
Regards,
Brandon
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Gaja Krishna Vaidyanatha
May I suggest Richard Foote's awesome presentation on Index Internals available
at - http://www.dbafan.com/book/oracle_index_internals.pdf.
________________________________
From: David Fitzjarrell <oratune@xxxxxxxxx>
Then data is inserted into the index always on one side (the most recent).
Data is always deleted on the other side (the least recent).
Not true as Oracle implements a balanced B-tree index structure where all leaf
nodes are the same depth and leaf blocks are, on average, 75% full.
________________________________
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.
Other related posts:
