index rebuilding...

I want to write a dynamic script to build the rebuild statement with the
optimum storage params for each index, either that or find a script
that does such a thing, but my initial searches have proven fruitless
for what I want to do. Which dd tables/views should I be hitting for
this query to build the rebuild statement?

suggestions?

TIA

alter index rebuild can rebuild the index without knowing it's column
definition.
That said you would only need dba_indexes and dba_segments (the segment_name
equals the index_name)
I saw a script today, but unfortunately I don't remember the author.
The commercial version of Toad has a separate module which allows you to
point and shoot at the problem.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

I could have swarn I read something either in Tom Kyte's book or on his site
stating that rebuilding indexes is a bad idea? I saw something about how an
index will eventually get to a state of equilibrium and you may experience
slowdowns while it reestablishes that equilibrium.

you know any documentation on this? about when to rebuild indices and such?

select 'alter index <OWNER>.'||segment_name||
' rebuild tablespace INDEX storage(initial '||round(bytes/1024)||
'K next '||round(next_extent/1024)||'K pctincrease 0);'
from dba_segments
where owner = '<OWNER>'
and tablespace_name not like '%INDEX%'
and segment_type = 'INDEX'

This moved the index to the correct tablespace, and set the initial
extent to the size of the index in K, and next_extent to what it already
was set at, next I will find all of the indexes that have a pctincrease
> 0 and adjust accordingly, and I think I should be able to use this
same script to modify the storage for all the indexes with out-to-lunch
storage settings by modifying the next_extent clause to a standard size
of 1M or 5M.

Still like to hear opinions if there is a better way to do this....

TIA

Frank (Germany)

Glen A Stromquist <glen_st...@no.spam.yahoo.com> schrieb in im
Newsbeitrag: FSYZ9.61355$c41.1...@news2.telusplanet.net...

You would have done. Tom claims (or, I should say, once told me) that he's
only ever rebuilt an index about 8 times in his working life.

You might also find a snippet on this subject in 'Beginning Oracle
Programming' (Wrox) -end of shameless plug- in the quite brilliantly-written
chapter on Indexes. Jonathan Lewis also has some advice on the matter in his
Practical 8i book.

The point Tom was making, and one I did some testing on, is that an index
may grow and grow and grow, and in the process acquire a rather large number
of extents, many of which may contain bits of empty space as a result of
updates and deletes on the associated table. If you rebuild the index, then
sure enough the index will be compacted down to a smaller size (and hence
fewer extents). Great. Except that presumably DML on the table doesn't
suddenly come grinding to a halt, so that the very next bit of DML that
happens on the table will cause the index to start getting bloated again.
And (here's Tom's particular point) as the index starts getting bloated
again, it has to *re*acquire the extents which it had already gone to the
trouble of acquiring before the rebuild. In dictionary-managed tablespaces,
extent acquisition can be an expensive process (especially if there's
contention for the UET$ and FET$ dictionary tables which are involved).
Therefore, performance can actually slow down after an index rebuild,
because you have extent allocations to deal with. The index itself also has
to re-organise itself as DML takes place on the table, with block splits and
so on. Before the rebuild, there was plenty of empty space into which new
entries could be fitted without inducing heaps of cascading re-organisation.
With a freshly-compacted index, re-organisation can be expensive. I confess
that most of my testing indicated not much performance degradation after a
rebuild, but some of it did. So it can happen.

Jonathan makes much the same point in his book: it's daft to constantly
rebuild indexes in pursuit of a mythical 100% efficiency, because continuing
DML on the table means that the nanosecond you achieve 100% efficiency, you
start losing it again (fresh block splits etc etc etc).

There's also the fact that a hoary old myth still exists that Oracle never
re-uses deleted space in an index. But that's just not true. If you delete
from EMP where name='KELLY', and then insert into emp (name) values 'KIM'
then you will indeed (probably) re-use the index entry space for the dead
KELLY entry. Meaning that your second insert does NOT have to induce a block
split, and that an analysis of the index would reveal zero deleted leaf
rows. In other words, Oracle uses index space rather more efficiently than
many people think, and as a result the initial *need* for an index rebuild
is rather lower than many people think.

Tom went so far as to say that 'only truly degenerate indexes' should ever
need to be rebuilt. I wasn't so much of a true believer, I confess, so I
watered that down to 'an occasional rebuild' *might* be appropriate.

Bearing in mind that even online rebuilds take exclusive table locks at the
beginning and end of the rebuild (and an offline rebuild takes an exclusive
lock for the duration of the rebuild, of course) then the costs of rebuilds
can be considered large, and so you are weighing a small benefit of
increased efficiency (which, as explained above, might not be much of a
benefit in any case) versus a big drawback of exclusive locking, lots of I/O
and so on.

Net result: rebuilds should be rare.

Regards
HJR

I think I may just do that, but the database compatible is set a 8.0.5
so I am waiting to hear from the vendor if there is a reason it has to
be at that, as I need it at 8.1.x to have locally managed tablespaces.
Then I need to research in to setting uniform allocation or letting
Oracle do it.

cheers!

> <whole lot of snipping>

You have just brought up an interesting question I've never heard addressed.
Tables and indexes are all just segments and a segment is a segment is a
segment. With a table, if you empty a table with a delete there is a high water
mark that when a full table scan takes place forces a read of every block. What
happens with an index scan? Does it also read all of the empty index blocks? My
guess is that it must because it has no way to know when to stop.

So while an index may begin becoming inefficient again after a rebuild, what
doesn't, there is presumably a point at which there is a net benefit to a
rebuild. And can we identify some guidlines around where that is?

Comments appreciated.

Daniel Morgan

Hi Daniel,

It depends on what you mean by an "Index Scan".

If you mean a "Full Index Scan" or an "Index Range Scan", then no,
these empty blocks have no effect as they're not part of the current
index structure and are therefore not traversed by Oracle.

If however you mean a "Fast Full Index Scan", then yes we have a
potential issue as Oracle performs a multiblock read and has no choice
but to read in these empty blocks until it reaches the HWM of the
index segment. And just like a table, if we have heaps of empty blocks
below the HWM, such scans are going to be correspondingly inefficient.

Note however that should such a situation arise, the corresponding
table is likewise going to be inefficient and benefit from a reorg
anyway (unless a large insert is imminent).

Cheers

Richard

That is what I was assuming. Thanks for the confirmation.

Daniel Morgan

It's perfectly okay to say that the article that
comes up is one of yours - after all, a user who
is searching google in their local language may
have something completely different as the
second hit.

A couple of points, by the way:

Oracle B-tree indexes are balanced: every leaf
is the same height from the root, it is not possible
for some leaf blocks to have height three and others
to have height four. (This is an error I used to make
as well, before I sat down and though about block
splits).

The 'gets per key' is affected by the typical number
of table rows per key - so rebuilding the index because
it exceeds any particular level is unlikely to be of any
benefit. The formula for this column is roughly:
height + half of (rows in index / distinct keys in index)

I can't think why the factor of a half has been included
though.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Don Burleson wrote in message
<998d28f7.0301...@posting.google.com>...
>Hi,
>
>Do a Google search on "oracle index rebuild".
>
>The second hit has a good article on the topic.

I never thought I'd ever try to correct you but I believe (sticking it
way out there on ths one) it is possible for there to be four on one and
three on the other from time-to-time as Oracle doesn't rebalance every
time someone performs a single insert or delete. Isn't it more a case of
never having 5 and 3?

Daniel Morgan

What sort of advice is that? Do you really think Google will always return
the same results in the same order, globally and forever? Why not just
provide the link directly (which, for anyone interested, I imagine was
intended to be Don's own article at http://www.dba-oracle.com/art_index1.htm

As it is, it's one of the weakest, error-ridden pieces of writing I've seen
in a long time, and certainly is NOT a "good article on the topic". Let's
start with the first line, shall we:

"Indexes require rebuilding when deleted leaf nodes appear". I don't even
know what a deleted leaf node is. Do you mean 'deleted leaf rows'? In which
case, you're wrong, because deleted leaf rows can disappear just as easily
as appear. Oracle re-uses deleted leaf row space, all on its own.

Next. " Oracle index nodes are not physically deleted when table rows are
deleted". I would have more confidence in the advice you offer if you could
get the distinction between a node (ie, a block)and a row right. You go on
"deleted leaf nodes can be easily identified " and then show a report one of
whose headings is, in plain black and white, 'Deleted Leaf Rows'.

As for this: "Note that Oracle indexes will "spawn" to a fourth level only
in areas of the index where a massive insert has occurred, such that 99% of
the index has three levels, but the index is reported as having four levels.
" ... well, all I can say is: this is complete baloney. Oracle's index
structures are B*Trees, meaning that they are 'balanced' trees. Meaning that
if it takes you three steps to get to the leaf nodes on one side of the
index, it is utterly impossible for it to take 4 on the other side. Oracle
guarantees that the height of the index is always balanced across the entire
index. the situation you describe does not, cannot and will not happen in
Oracle.

"Another rebuild condition would be cases where deleted leaf nodes comprise
more than 20% of the index nodes". Not true. You may well have more than 20%
of your leaf ROWS shown as being deleted. But they'll be re-used if you wait
long enough, because Oracle re-uses the space:

SQL> create index emp_ename_idx on emp(ename);
Index created.

SQL> analyze index emp_ename_idx validate structure;
Index analyzed.

SQL> select lf_rows, del_lf_rows from index_stats;

LF_ROWS DEL_LF_ROWS
---------- -----------
19 0

[ie, no deleted leaf ROWS (and please note Oracle's own terminology)]

SQL> update emp set ename='ZEBEDEE' where ename='ALLEN';
1 row updated.
SQL> commit;
Commit complete.

SQL> analyze index emp_ename_idx validate structure;
Index analyzed.

SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
20 1

[Oh Lord, a deleted leaf row has appeared!!! Better get ready to rebuild, I
suppose. But hang on...]

SQL> insert into emp (empno, ename) values (9993, 'ABBOT');
1 row created.
SQL> commit;
Commit complete.

SQL> analyze index emp_ename_idx validate structure;
Index analyzed.

SQL> select lf_rows, del_lf_rows from index_stats;

LF_ROWS DEL_LF_ROWS
---------- -----------
20 0

[Phew! A fresh insert has gotten rid of the deleted leaf row, meaning that
the space has been re-used. And note the original entry was 'ALLEN' and the
new one was 'ABBOT', so it's not a question of needing a new entry identical
to the old before the space is reused. In point of fact, an insert that
makes use of a deleted leaf row space clears out ALL deleted leaf rows in
that block, so if I'd updated 'ALSOP', 'ALBERTT', 'ATHGUARD' and 'ATHLON',
the insertion of 'ABBOT' would have removed all four deleted leaf rows. As
proof:

insert various new rows into EMP;
SQL> select ename from emp
2 where ename like 'A%';
ENAME
----------
ABBOT
ADAMS
ALBERT
ALSOPP
ATHERGUARD
ATHLIN

SQL> drop index emp_ename_idx;
Index dropped.

SQL> create index emp_ename_idx on emp(ename);
Index created.

SQL> analyze index emp_ename_idx validate structure;
Index analyzed.

SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
24 0

Now time for the updates.

SQL> update emp set ename='ALSOP' where ename='ALSOPP';
1 row updated.
SQL> update emp set ename='ALBERTT' where ename='ALBERT';
1 row updated.
SQL> update emp set ename='ATHGUARD' where ename='ATHERGUARD';
1 row updated.
SQL> update emp set ename='ATHLON' where ename='ATHLIN';
1 row updated.
SQL> commit;

SQL> analyze index emp_ename_idx validate structure;
Index analyzed.

SQL> select lf_rows, del_lf_rows from index_stats;

LF_ROWS DEL_LF_ROWS
---------- -----------
28 4

SQL> insert into EMP (empno, ename)
2 values (4453, 'ABBOT');
1 row created.
SQL> commit;
Commit complete.

SQL> analyze index emp_ename_idx validate structure;
Index analyzed.

SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
25 0

So one insert cleans out 4 deleted entries, making their space available for
re-use. And not a rebuild in sight.

If this were an index on a sequence number, then yes -the nature of
sequences is such that you'll never be assigned a new number which could
make use of the previously-deleted rows, so a rebuild MIGHT be warranted.
(But I'd try a coalesce first, if I had 8i -and if your article was only
ever written for Oracle 7, then don't you think it's a bit 'off' offering it
as 'good advice' on index management 3 major releases later?)].

Next... "As you may know, you can easily rebuild an Oracle index with the
command: ALTER INDEX index_name REBUILD". You're kidding, right?? "easily
rebuild"???? Never mind the vast amounts of I/O, the exclusive table
locking. Oh, I see... your description of what a rebuild does talks about it
'walking the existing index' and 'populating temporary segments', but the
minor matter of locking out all users from performing DML on the indexed key
doesn't warrant a mention. Uh huh.

I won't even get into whether the statement that 'bitmapped indexes are
faster when the index key has less than 25 distinct values' has any validity
at all. Faster at what, and than what? And why 25? You mean a bitmap index
with 26 distinct values is going to be slower? Unacceptably slower?
Imperceptibly slower? Than knitting? Than using a b*tree index? Than doing a
full table scan? The number of distinct values for which a bitmap index
might be beneficial is *relative* to the number of records in the table, and
there is no specific cut-off point at which they begin to be useful or cease
to be useful. The number 25 is a figment of your imagination.

And I notice you cheerfully recommend converting a b*tree index into a
bitmap one if the magic number 25 is true, without once mentioning the
horrendous side effects of doing so in a DML-intensive environment.

I dunno. Maybe I'm missing something. But this article is so trite,
incorrect, full of wrong terminology, and downright misleading that it
wouldn't be my first recommendation (or indeed any recommendation at all)
for anyone wanting to know something meaningful about index rebuilds. Maybe
that's why you didn't include the actual article link?

HJR

Jonathan can answer for himself, of course. But I'll take a stab at it
too... no, it's not possible for a single insert to result in an unbalanced
index. Oracle's indexes are *always* balanced. That's why you really don't
want to create indexes unnecessarily, because they can slow down simple DML
on the table dramatically. Your simple insert that threatens to unbalance an
index will cause a whole raft of cascading adjustments to the index
structure precisely to avoid imbalance.

I don't know whether it's just a myth or not, but Oracle refers to its
indexes as "B*Tree" structures, not 'b-tree' ones. And that's because the
'b' doesn't stand for 'binary', but 'balanced'.

Whether that bit of apocrypha is true or not, Oracle index structures are
always re-balanced.

Regards
HJR

The mechanism on inserts is simple and recursive:
If you need to insert an index entry and the leaf
is full, then it splits. But if it splits, the branch block
above it has to have an extra entry (pointing to
the value that starts the new leaf block) - so Oracle
has to insert an entry into the branch block.

But if the branch block is full, it has to split - which
means the branch block above it has to have a
new entry - but if that block is full ..... and so on
until the block above is the root block.

So what do you do if the root block is full ?
Split it in two, and create a new root block
above it with just two entries - at which point
EVERY leaf block has changed height.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

____UK_______March 19th
____USA_(FL)_May 2nd

____USA_(CA, TX)_August

DA Morgan wrote in message <3E3AAC94...@exesolutions.com>...

Just went through my files and found a posting by Jonathan from 23 May, 2002
subject title "unbalance indexes --common widsom?". And I quote:

"No matter how perfectly an index is maintained, it will always be possible to
ensure that you can get it to a state where either just one 3rd level node has
split to produce an "imbalance" or (the only possible alternative) every 3rd
level node has to split to leave the index half-populated if you want "balance"
- i.e. every leaf has to be at the fourth level.

The important point about balance b-trees is that no leaf is MORE THAN ONE level
deeper that every other leaf."

(even the above misspelling accurately copied).

So unless Jonathan has changed his mind ... my recollection is correct.

Jonathan?

Daniel Morgan

Regards
HJR

news:3E3B0D3F...@exesolutions.com...

So now you ARE speaking for him. <g>

Thanks.

Daniel Morgan

Hi Daniel,

I remember these posts as it was around the time I appeared on the scene !!

I questioned him at the time and he subsequently corrected himself later in
the thread.

Indexes are *always* balanced.

Cheers

Richard

Regards
HJR

Thanks, Richard.
Regards
HJR

The point, which appears to have sailed straight over your head, is that
they are called leaf ROWS, not leaf NODES. As the report in your article
actually goes on to show.

>Despite your delusions of knowledge about
> a "pure" B*Tree, an Oracle index tress cannot be a pure b*tree because
> it is impossible to index 100m rows in only four levels in a b*tree.
> Did you flunk your basic data structures class?

Er, where exactly did I say that such an index had to have a height of four?
Nowhere, that's where. What I said, and which is true, is that if its 4 on
one side, it is 4 on the other, not 3 and 4 as you wrote in the article.

> Oracle maintains many sub-keys within each inode.
> When the number of
> entries exceeds the internal "index_block_contains" threshold
> (normally 50, depending on the key length), the Oracle index splits
> until it reaches the maximum supported level of its superior node. At
> that point, the index "spawns" into another level.

Big deal. We know this. What you've missed out that if it spawns a new
level, there's a deal of re-organisation in the branch blocks (and the root
block, if required) to maintain a consistent height across the entire index.

> I cannot
> comprehend that you think that Oracle will re-level a whole index tree
> in real-time. Think about it!

I have. It does. Don't believe me? Ask Jonathan. Or Tom Kyte. Or Steve
Adams. Or, come to that, me.

> Yes, Einstein, in your simplistic example Oracle will re-use an
> adjacent deleted node that rarely happens in the real-world. In most
> cases, indexes with significant DML will have large amounts of dead
> leaf nodes.

Leaf rows you mean.

And how you can say that beats me. Unless your index is on a monotonically
incrementing sequence number, then you can't rule out the possibility of an
insert or an update wanting to make use of deleted space. So where you have
'significant DML' is precisely where the deleted leaf rows will be re-used.
Assuming that your DML is broadly-speaking 'randomized'.

And why you persist in inventing your own terminology for what Oracle has
already perfectly clearly named also beats me.

But it doesn't surprise me.

> Yes, I agree, one of us does not know what they are talking about.

Please do a describe on INDEX_STATS. Please report back what the column
headings are. If there's one called 'LEAF_NODES', I'll eat copious
quantities of Humble Pie. Oh, alright. Tell you what: I'll do it for you.

HEIGHT
BLOCKS
NAME
PARTITION_NAME
LF_ROWS
LF_BLKS
LF_ROWS_LEN
LF_BLK_LEN
BR_ROWS
BR_BLKS
BR_ROWS_LEN
BR_BLK_LEN
DEL_LF_ROWS
DEL_LF_ROWS_LEN
DISTINCT_KEYS
MOST_REPEATED_KEY
BTREE_SPACE
USED_SPACE
PCT_USED
ROWS_PER_KEY
BLKS_GETS_PER_ACCESS
PRE_ROWS
PRE_ROWS_LEN
OPT_CMPR_COUNT
OPT_CMPR_PCTSAVE

Gosh. Not a "LEAF_NODE" column in sight, though I see one called "LF_ROWS"
and another called "DEL_LF_ROWS". Guess I have to go hungry then.

If you're going to post to an *Oracle* newsgroup, citing an article
addressing *Oracle* index issues, then it behoves you to stick to *Oracle*
terminology. It also behoves you to get it right, but that's a tad harder to
pull off, obviously.

> You really should take the time to check your facts before you
> embarrass yourself.

Hi Kettle. You must be Pot.

> No genius, look at the DESCTs. If you take the trouble to decompile
> the code and look at a few block dumps, you can see exactly how it
> works.

Been there, done that. You're wrong.

Your index article is about as good as some of the other performance tuning
stuff you've published in the past. IE, not very.

HJR

Thank you all for this spirited, if somewhat testosterone-laden, celebration of
index balancing.

My students will no doubt continue to benefit from your knowledge and wisdom.

Now about that Macallan I had last evening.

Daniel Morgan

Now, clearly, I appear to be giving out an impression which it wasn't my
intention to give. So I just want to clarify: whilst Don might throw in the
odd 'Einstein' comment, and other bits of sarcasm directed against me
personally, I have criticised the content of his *writing*. I don't know Don
Burleson from Adam, and I'm sure he makes a pretty mean Pavlova whilst
juggling icicles on a hot summer's day.

But his article is trash. And whilst my prolix prose might give the
impression of testosterone oozing with every key-press, that wasn't my
intention, and I apologise to the extent that anyone's got that impression.

I just can't stand people trotting out technical garbage and then getting
personal about it when called on it. That's all.

In the spirit of backing off, I just quote two small passages:

Don Burleson: "I cannot comprehend that you think that Oracle will re-level

Jonathan Lewis: "The mechanism on inserts is simple and recursive: If you

Which is precisely what I said, but I realise no-one believes it when I say
it.

Ughhh. Jamieson's or nothing, I'm afraid. Or, if all else fails, this rather
fine 1993 Shiraz wot I'm quaffing as we speak.

And who was posting about Bushmill's Paintstripper??? I'd rather rebuild an
index, thanks all the same!!

Regards
HJR

I think that's the thread where someone suggested
I was talking rubbish - and where I subsequently
sat down and thought it through and decided that
I was talking rubbish. I think you'll even find my
retraction and explanation further on in that thread.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

____UK_______March 19th
____USA_(FL)_May 2nd

____USA_(CA, TX)_August

DA Morgan wrote in message <3E3B0D3F...@exesolutions.com>...

Pray tell me: where did the magic number 25 come from?

And why was there not the slightest mention of the inadvisability of bitmap
indexes in a DML-heavy environment??

And why did you say that index rebuilds were "easy", when in fact they
require exclusive table locks (even with the 'online' option in 8i and
higher) and a great deal of performance-degrading I/O?

Did you just run out of space in your article, or what?

And since you deem my examples demonstrating deleted index entry re-use to
be too simplistic and not-real-world, can we see your real-world tests
demonstrating that deleted leaf rows *won't* be re-used????

Oh: and one more thing. The re-use of deleted leaf rows I demonstrated did
not require the new entries to be 'adjacent' to the old ones, as you
claimed. The first entry made into a leaf node that contains deleted entries
causes *all* deleted entries for that node to be cleaned out, regardless of
the specific value. An entry of 'BOLTON' would have cleared out all my 'A%'
deleted entries, because all of them were in the same block.

HJR

news:998d28f7.03013...@posting.google.com...

> Yes, that is very apparent. Despite your delusions of knowledge about

> that point, the index "spawns" into another level. I cannot

> in real-time. Think about it!

Comments embedded

news:998d28f7.03013...@posting.google.com...

Not sure what you mean here. Are you saying that 100m row index can't fit in
a 4 level index ? Well it can, although the block size and index key length
would have a say I would think. Or are you suggesting that Oracle indexes
can only have 4 levels ?

Sorry Don, this is incorrect. Although there is some overhead to an index
splitting, I've always thought Oracle was rather efficient in the way it
maintained the index balance. Worst possible case (assuming a 3 level index
about to jump to 4 to keep balanced):

- leaf block splits, extra IO (probably) as Oracle grabs the next free block
on the freelist
- if the last 'block' in the index structure then new index entry simply
goes to the newly added block, else 50% of the existing entries go into each
split block and the new value goes where appropriate
- corresponding branch 'block' needs to be updated to add an entry that
points to the newly added block but if full, it too is split, extra IO
(probably) as Oracle grabs the next free block on the freelist
- same as the leaf block, existing entires get redistributed and new branch
pointer is added
- root block needs to be updated to add an entry that points to the newly
add branch block but if full, it too is split, extra IO (probably) as Oracle
grabs the next free block on the freelist
- same as the branch block, existing entires get redistributed and the new
branch pointer is added
- a new block is required for a new root node, extra IO (probably) as Oracle
grabs the next free block on the freelist
- pointers are added to point to the two (now) second level branch nodes

Index rebalance is complete. So in total, we had to split 3 blocks and
redistribute (possibly) their contents and perform 4 additional IOs. Now our
poor little insert (or possibly update) has had to wait for all this to
complete and yes there is some overhead, but such a "total" rebalance would
occur once in a blue moon and isn't "that" huge a one (IMHO).

Now I'm confused. If you mean leaf "rows", then note these are completely
reusable by subsequent inserts. If you mean leaf "blocks" then these are no
longer part of the index structure and except for fast full index scans,
have no effect on the performance of the index. These dead "things" are only
a concern if:

1. The table has "shrunk" and the same volume of rows is not expect to be
reinserted. In which case, a table rebuild as well as an index rebuild is in
order

2. The index has incremental entries and the deleted rows are not going to
be reused by subsequent inserts. This is the scenario when an index rebuild
is most likely to be of use (if some but not many entires remain in a whole
bunch of existing leaf blocks).

Hummmm. I would love to see evidence to the contrary because the block dumps
I taken over the years support my understanding on how indexes work.

Create a table, create an index, populate it slowing and dump to see how the
index "grows" and keeps balanced. Then delete rows slowing and reinsert
again and you'll see exactly how Oracle uses it's indexes and reuses the
deleted space. You see "pointers" or slots that get reused and map to the
newly insert index entries.

Report back on your findings ;)

Cheers

Richard

PS. I'm no Einstein but a David Bowie fan who got ripped off today on one
exceedingly bad haircut !!

You can say that again.

>
> My understanding of Oracle indexing was based on "borrowed" 7.3.3
> source code (donated by a disgruntled ex-Oracle employee), and the
> article that the arrogant, insulting, and self-aggrandizing Howard
> calls "rubbish" was published in back in 1995, when 7.3.4 was just
> released.

You posted in February 2003 that I knew nothing about it, that I was
suffering from "delusions of knowledge", that "one of us doesn't know what
they are talking about", and (here's the classic) "You really should take

Only now do we discover that your information is 8 years out of date (though
a modicum of testing in that time would have put you straight).

You think it's OK to recommend 8-year-old information that is technically
inaccurate, do you?

It always amuses me to see people whingeing about how "arrogant" and
"insulting" I am when I point out they're wrong.

> Each data block within the index contains "nodes" in the index tree,
> with the bottom nodes (leaf blocks), containing pairs of symbolic keys
> and ROWID values. As an Oracle tree grows (via inserting rows into
> the table), Oracle fills the block, and when the block is full, it
> splits, creating new index nodes (data blocks) to manage the symbolic
> keys within the index. Hence, an Oracle index block may contain
> pointers to other index nodes or ROWID/Symbolic-key pairs.
>
> The number of entries within each index data block is a function of
> two values:
>
> 1 - The length of the symbolic key
> 2 - The blocksize for the index tablespace
>
> Because the blocksize affects the number of keys within each index
> block, it follows that the blocksize will have an effect on the
> structure of the index tree. All else being equal, large 32k
> blocksizes will have more keys, resulting in a flatter index than the
> same index created in a 2k tablespace.

In plain English, big blocks store more stuff than small blocks. Absolutely
true. That nugget of information, at any rate, will still be true in 8
years' time.

>
> According to an article by Christopher Foot
> (www.dbazine.com/foot3.html):
>
> "A bigger block size means more space for key storage in the branch
> nodes of B-tree indexes, which reduces index height and improves the
> performance of indexed queries."
>
> (Oh dear, Mr. Foot used the "node" word. I'll bet Howard will be
> really pissed . . . .)

Not at all. He used the term correctly. An index is comprised of a root
node, branch nodes and leaf nodes, within which are stored leaf entries. Mr
Foot is referring to branch nodes perfectly properly: Unlike you, he hasn't
referred to the entries themselves as nodes, but to the blocks within which
they are stored.

> In any case, there appears to be evidence that block size affects the
> tree structure,

That much we've known for years.

>which supports the argument that data blocks (yes
> "nodes"),

So, you finally acknowledge the true terminology. Progress, I suppose. Now,
if you could just also acknowledge that your 8-year-old article is not a
technically-accurate description of index behaviour in 8.0, 8i, 9iR1 and
9iR2, we might be on to something.

>affect the structure of the tree.

HJR

I don't think they have changed.

So your article may well not turn up as the second reference in Google
and too many posters here don't search Google before posting.

>
>At that time, (and I have the source code filed away somewhere) Oracle
>index nodes were organized by data blocks, where each data block was
>an index node.

I don't think this has ever been true. An index leaf block will
always contain multiple leaf rows. You seem to imply each data block
has only one index node, whatever that may be.

That is why you cannot specify PCTUSED for an index,
>because Oracle must govern the freelist re-link during splitting and
>spawning operations.
>
>Please correct me if any of the following is incorrect:

If this is a truly B*tree algorithm, it will split the index bucket,
turning it into a non-leaf bucket, creating two new buckets, and
balance the tree. An index block is either contains leaf rows or
branch rows, by virtue of the (generally available) algorithms, it can
*NEVER* contain a MIXTURE of leaf and non-leaf rows.
If you look at the index_stats results after an ANALYZE INDEX
that is EXACTLY what you are looking at.

>
>The number of entries within each index data block is a function of
>two values:
>
>1 – The length of the symbolic key
>2 – The blocksize for the index tablespace
>

AND the room for locking info
AND the freelist to mention only a few things.

I would consider this a myth. The only thing that would improve
performance is a decreased depth of the index. Having observed depth
behavior at various block sizes, I can safely say the depth of an
index will almost always be 3, or 4, and a depth of 2 implies a very
small table.

>tree structure, which supports the argument that data blocks (yes
>"nodes"), affect the structure of the tree.

Which evidence? There isn't any, at least not in your response.
The only thing which does hold true is that size of the block header
is more or less fixed, and the net available storage per block is
bigger. My experience however is that this doesn't result in indexes
with a smaller depth.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

Comments embedded (warning, a bit long in places ...)

news:998d28f7.03020...@posting.google.com...

I'm not sure as I was only a baby in nappies back them ;) However, I'm
pretty sure that at least version 7 of Oracle had a similar algorithm.

> an index node. That is why you cannot specify PCTUSED for an index,

The reason why you can't specify a PCTUSED is that indexes behave quite
differently from (conventional) tables in that if there's space in a node,
Oracle will simply use it, if there isn't then Oracle simply splits the
block. The concept of having a block *within the index structure* not being
available for insert and having to await reaching a PCTUSED mark is
therefore not applicable. In summary, if an index block is currently
allocated to the index structure, it must by definition be off the freelist.

This is true expect that a particular "node" is either a "leaf node" which
means it contains an range of index entires along with their associated
rowids or is a "branch node" which means it contains a set of range pointers
that point to other branch nodes or leaf nodes within the index structure.
These "pointers" are represented by a "less than" value from which the
required path through the index structure can be derived. Note a particular
block within the index structure can only be a leaf or a branch node.

Like I mentioned earlier, the best way to see all this is via a few block
dumps. Following is an extract from a dump of a "branch" block. I don't want
to necessarily describe the whole structure but the odd comments with a ***
are mine:

Start dump data blocks tsn: 9 file#: 9 minblk 1420 maxblk 1420
buffer tsn: 9 rdba: 0x0240058c (9/1420)
scn: 0x0000.019233ab seq: 0x01 flg: 0x00 tail: 0x33ab0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0240058c
Object id on Block? Y
seg/obj: 0x79a7 csc: 0x00.192339f itc: 1 flg: E typ: 2 - INDEX ***
index object
brn: 0 bdba: 0x2400589 ver: 0x01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn
0x0000.0192339f

Branch block dump *** Block dump highlights this block as a branch node
=================
header address 49221708=0x2ef104c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 69
kdxcofbo 166=0xa6
kdxcofeo 6907=0x1afb
kdxcoavs 6741
kdxbrlmc 37750157=0x240058d
kdxbrsno 0
kdxbrbksz 8060
row#0[8043] dba: 37750158=0x240058e *** first branch pointer
col 0; len 5; (5): 4d 44 53 59 53 *** go this way for
values less than this value (MDSYS)
col 1; len 6; (6): 02 40 04 ba 00 38 *** location of "pointed
to" node (in this case a leaf node)
row#1[8028] dba: 37750159=0x240058f *** second branch pointer, etc ...
col 0; len 3; (3): 4f 44 4d
col 1; len 6; (6): 02 40 04 bf 00 2a
row#2[8007] dba: 37750160=0x2400590
col 0; len 9; (9): 4f 45 4d 5f 5a 49 47 47 59
col 1; len 6; (6): 02 40 04 ac 00 42
row#3[7986] dba: 37750161=0x2400591
col 0; len 9; (9): 4f 45 4d 5f 5a 49 47 47 59
col 1; len 6; (6): 02 40 04 c0 00 2d

And this is an extract of the first leaf node that is being pointed to by
the above branch node...

Start dump data blocks tsn: 9 file#: 9 minblk 1421 maxblk 1421
buffer tsn: 9 rdba: 0x0240058d (9/1421)
scn: 0x0000.019233a1 seq: 0x01 flg: 0x04 tail: 0x33a10601
frmt: 0x02 chkval: 0x1090 type: 0x06=trans data
Block header dump: 0x0240058d
Object id on Block? Y
seg/obj: 0x79a7 csc: 0x00.192339f itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2400589 ver: 0x01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn
0x0000.0192339f

*** above are the two (minimum) transaction slots

Leaf block dump *** OK, this is a leaf node ...
===============
header address 49221732=0x2ef1064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 413
kdxcofbo 862=0x35e
kdxcofeo 1680=0x690
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 37750158=0x240058e
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8021] flag: -----, lock: 0 *** first index entry header
(currently unlocked)
col 0; len 5; (5): 42 4f 57 49 45 *** first index entry value
(BOWIE)
col 1; len 6; (6): 02 40 20 60 00 29 *** rowid of first index entry
row#1[8006] flag: -----, lock: 0 *** second index entry, etc ....
col 0; len 5; (5): 42 4f 57 49 45
col 1; len 6; (6): 02 40 20 60 00 2a
row#2[7991] flag: -----, lock: 0
col 0; len 5; (5): 42 4f 57 49 45
col 1; len 6; (6): 02 40 20 60 00 2b
row#3[7976] flag: -----, lock: 0
col 0; len 5; (5): 42 4f 57 49 45
col 1; len 6; (6): 02 40 20 60 00 2c

When you delete an index entry, it looks like this :

SQL> delete insert_bowie where owner = 'BOWIE' and rownum = 1;

1 row deleted.

Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE
1 0 DELETE OF 'INSERT_BOWIE'
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'INSERT_BOWIE'
4 3 INDEX (RANGE SCAN) OF 'INSERT_BOWIE_IDX' (NON-UNIQUE
)

SQL> commit;

Commit complete.

Start dump data blocks tsn: 9 file#: 9 minblk 1421 maxblk 1421
buffer tsn: 9 rdba: 0x0240058d (9/1421)
scn: 0x0000.0192ad54 seq: 0x01 flg: 0x00 tail: 0xad540601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0240058d
Object id on Block? Y
seg/obj: 0x79a7 csc: 0x00.192339f itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2400589 ver: 0x01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
0x0000.00000000
0x02 0x000a.01f.0000703f 0x008003ce.022c.2a ---- 1 fsc
0x0011.00000000

*** Note here that the second slot has/had a transaction (which happens to
be the transaction that deleted an index entry)

Leaf block dump
===============
header address 99618916=0x5f01064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 413
kdxcofbo 862=0x35e
kdxcofeo 1680=0x690
kdxcoavs 818
kdxlespl 0
kdxlende 1
kdxlenxt 37750158=0x240058e
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8021] flag: ---D-, lock: 2 *** Note here that the first entry is/has
been locked by the transaction in transaction slot number 2. Also note that
the entry has been marked as deleted "D".

col 0; len 5; (5): 42 4f 57 49 45 *** This is hence now a "deleted
row" entry
col 1; len 6; (6): 02 40 20 60 00 29
row#1[8006] flag: -----, lock: 0
col 0; len 5; (5): 42 4f 57 49 45
col 1; len 6; (6): 02 40 20 60 00 2a

Now to see how this deleted entry gets reused, one final dump (I promise )

SQL> insert into insert_bowie (owner) values ('AADVARK');

1 row created.

SQL> commit;

Commit complete.

Start dump data blocks tsn: 9 file#: 9 minblk 1421 maxblk 1421
buffer tsn: 9 rdba: 0x0240058d (9/1421)
scn: 0x0000.0192b7b8 seq: 0x01 flg: 0x02 tail: 0xb7b80601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0240058d
Object id on Block? Y
seg/obj: 0x79a7 csc: 0x00.192b7b3 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2400589 ver: 0x01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
0x0000.00000000
0x02 0x0005.022.0000668b 0x00800a3f.01ff.29 --U- 1 fsc
0x0000.0192b7b8

*** Again note, we have/had a transaction going on in slot 2

Leaf block dump
===============
header address 99618916=0x5f01064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 413
kdxcofbo 862=0x35e
kdxcofeo 1663=0x67f
kdxcoavs 816
kdxlespl 0
kdxlende 0
kdxlenxt 37750158=0x240058e
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[1663] flag: -----, lock: 2 *** this entry is/has been
locked by a transaction in slot 2
col 0; len 7; (7): 41 41 44 56 41 52 4b *** we now have an index entry for
the value (AADVARK)
col 1; len 6; (6): 02 40 20 61 00 24 *** a search through the whole
dump will find that all trace of the previously deleted index has been
erased.

row#1[8006] flag: -----, lock: 0
col 0; len 5; (5): 42 4f 57 49 45
col 1; len 6; (6): 02 40 20 60 00 2a

>
> The number of entries within each index data block is a function of
> two values:
>
> 1 - The length of the symbolic key
> 2 - The blocksize for the index tablespace
>

So the number of possible entries within an index block is a function of the
total length of the index column(s) values (although note that this can now
be compressed to be made more effiicient) and the size of the index block.

By having a larger block size, you can indeed fit more values into less
blocks resulting in less leaf nodes. If you have less leaf nodes, you
therefore don't need as many "pointers" to these nodes and so require less
branch nodes. If you can reduce the total number of branch nodes required,
you thereby reduce the size of the index structure sitting above the leaf
nodes which *might* result in a lower index height. However, because you can
fit so many entries into a particular branch node, and because so many
branch nodes can be pointed to by the root or intermediate branch nodes, you
generally have far fewer branch nodes allocated than the maximum capacity of
the index structure and so an actual reduction in index height is actually
quite rare.

I can't comment on my almost name sake (don't you just hate it when people
can't spell; fancy dropping off the last "e", it's so crucial to well
balanced name ;) but I would have written the comment above as "a bigger

indexes, which *might in some particular situations* reduce index height
*but nonetheless will improve the performance of indexed queries which
require a "range scan operation" as fewer leaf nodes now need to be
accessed, potentially reducing IO and associated overheads*". But then
again, I'm not Christopher ...

No argument there. Indexes love larger blocks sizes because they can fit
more data into fewer nodes meaning a more efficient structure. Regardless of
whether or not such savings represents a lower index, it will result in
fewer leaf nodes making index range scans more efficient (which is a pretty
popular way to use an index).

I hope this helps to clear a few things up.

Cheers

Richard

Not quite. B-trees aren't binary, either. They have basically the same
node layout with n-1 keys and n pointers/data items[1], and they are
also always balanced. What they do not have is the distinction between
branch nodes and leaf nodes. Each node can contain both pointers to
other nodes and data.

B+trees push all the data down to the leaf nodes and keep only pointers
to other nodes in the higher level nodes.

B*trees are like b+trees, but use a modified split algorithm to keep the
nodes at least 2/3 filled.

hp

[1] In the case of an index separate from the table, the "data" are
pointers into the table.

--
_ | Peter J. Holzer | To a database person,
|_|_) | Sysadmin WSR | every nail looks like a thumb.
| | | h...@hjp.at |
__/ | http://www.hjp.at/ | -- Jamie Zawinski

Correct me if I'm wrong, but aren't B*trees supposed
to redistribute leaf entries to adjacent blocks without
doing a split if certain conditions are met - and this is
something that Oracle never does.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

____UK_______March 19th
____USA_(FL)_May 2nd

____USA_(CA, TX)_August

Peter J. Holzer wrote in message ...

Akshally, the whole thing is terminally confusing.
According to Knuth, B+trees and B*trees are quite
different. And Oracle doesn't help with their
"modified algorithm" B+trees thing. It all adds to
the confusion.

I'd bet if someone spends some time going through the
actual index handling source code, it will come out
that the algorithm is neither one nor the other and
is in fact quite unique.

It's one of the few areas where makers can still have some
significant implementation differences, and of course
distinguishing factors. The only reason we don't see the
marketing dingbats pouring all over it is that the whole thing
is arcane, to put it very mildly.

--
Cheers
Nuno Souto
nso...@optusnet.com.au.nospam

Thanks to all.

Sounds to me like someone is going to be doing some block dumps pretty
soon.

Or at least I hope so. It would be great to have a definitive answer.

Daniel Morgan

I'm afraid just block dumps won't be enough for this one.
Source code would be needed, and fairly recent too.
Maybe one of the "Danish round table" blokes will do it, they
should have access to this.

Thing is: if you look at the definitions of B*tree and
B+tree on the Net as well as on Knuth's books, they
don't exactly match what is described in the Oracle literature,
or what happens inside them old blocks. And then we have
index key compression....

All that makes the Oracle implementation, IMHO, quite
difficult to pigeon-hole.

Hi Daniel,

*** WARNING WARNING WARNING ***

*** BLOCK DUMPS BLOCK DUMPS BLOCK DUMPS ***

The following is a very simple but hopefully illustrative demonstration of
how Oracle maintains it's indexes. It's not however meant to be a
"definitive answer".

So long as one follows the basic principles that once a leaf node is full,
it needs to "split" and by doing so the branch node that points to it
requires a new entry and if it's full, it needs to split etc., this will
show how Oracle puts it all together.

OK, firstly, lets build a simple table and associated index:

SQL> create table bowie_stuff (id number, album_title varchar(30));

Table created.

SQL> create index bowie_stuff_idx on bowie_stuff(album_title);

Index created.

Now let's populate with some data and get the index structure building
along. Note the values I'm using, they'll come in handy later.

SQL> insert into bowie_stuff values (1, 'David Bowie');

1 row created.

SQL> insert into bowie_stuff values (2, 'The Man Who Sold The World');

1 row created.

SQL> insert into bowie_stuff values (3, 'Hunky Dory');

1 row created.

SQL> insert into bowie_stuff values (4, 'Ziggy Stardust');

1 row created.

SQL> insert into bowie_stuff values (5, 'Aladdin Sane');

1 row created.

SQL> insert into bowie_stuff values (6, 'Pin Ups');

1 row created.

SQL> insert into bowie_stuff values (7, 'Diamond Dogs');

1 row created.

SQL> insert into bowie_stuff values (8, 'Young Americans');

1 row created.

SQL> insert into bowie_stuff values (9, 'Station To Station');

1 row created.

SQL> insert into bowie_stuff values (10, 'Low');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into bowie_stuff select * from bowie_stuff;

10 rows created.

SQL> /

20 rows created.

SQL> /

40 rows created.

SQL> /

80 rows created.

SQL> /

160 rows created.

SQL> /

320 rows created.

SQL> commit;

Commit complete.

Having played with this table structure for many years now, I know I'm close
to having a "full" index structure. Let's see the current state of affairs:

SQL> analyze index bowie_stuff_idx validate structure;

Index analyzed.

SQL> select height, lf_blks, br_blks from index_stats;

HEIGHT LF_BLKS BR_BLKS
---------- ---------- ----------
2 2 1

We currently have a nice simple index structure with one branch (root) node
pointing to two leaf nodes.

I now check to see which blocks I need to dump.

SQL> select file_id, block_id, blocks from dba_extents where segment_name =
'BOW
IE_STUFF_IDX';

FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
9 1513 8

What I'll do now is take a before and after dump of the branch and leaf node
block while inserting a new row. For the record, three further inserts did
the trick and caused the first leaf node to "split".

SQL> insert into bowie_stuff values (7, 'Diamond Dogs');

1 row created.

SQL> commit;

Commit complete.

So now I've "captured" what Oracle has done in order to maintain it's index
structure after a node split.

First of all, lets look at an extract of the branch node, just *before* the
leaf node split. My comments are preceded with a *** :

Start dump data blocks tsn: 9 file#: 9 minblk 1516 maxblk 1516
buffer tsn: 9 rdba: 0x024005ec (9/1516)
scn: 0x0000.01974320 seq: 0x02 flg: 0x02 tail: 0x43200602

Block header dump: 0x024005ec

seg/obj: 0x79ab csc: 0x00.197431c itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x24005e9 ver: 0x01
inc: 0 exflg: 0

*** above portion is the block header which includes the last SCN, rdba,
object type (2 index), etc. ***

0x01 0x0001.00b.00006e99 0x00800888.02ca.01 -BU- 1 fsc
0x0000.01974320

*** above is the transaction slot (used by Oracle to maintain branch node
structure) ***

Branch block dump
=================
header address 99815500=0x5f3104c

kdxcolok 1

kdxcosdc 1
kdxconro 1
kdxcofbo 30=0x1e
kdxcofeo 8041=0x1f69
kdxcoavs 8011
kdxbrlmc 37750255=0x24005ef *** An error with my previous post to Don
(which Jonathan Lewis kindly pointed out) is that this actually marks the
first "pointer" to the leaf nodes. The logic being that values > null but
less than the next pointer go to this leaf node. So 0x24005ef marks the
first rdba of the first leaf node ***
kdxbrsno 0
kdxbrbksz 8060

*** above section is the index (branch) header details, more on this later

row#0[8041] dba: 37750256=0x24005f0 *** This therefore marks the pointer
to the second leaf node in the index, all values greater than or equal to
col 0 belong in this leaf node. As there are only currently 2 leaf nodes in
this index, that's about it for now ***
col 0; len 7; (7): 50 69 6e 20 55 70 73 *** values greater than or equal
to PIN UPS go here ***
col 1; len 6; (6): 02 40 05 e8 00 eb
----- end of branch block dump -----
End dump data blocks tsn: 9 file#: 9 minblk 1516 maxblk 1516

Next, let's look at the poor index block in "desperate need of a bucket",
ie. she's about to blow (split) ;)

Start dump data blocks tsn: 9 file#: 9 minblk 1519 maxblk 1519
buffer tsn: 9 rdba: 0x024005ef (9/1519)
scn: 0x0000.01975306 seq: 0x01 flg: 0x02 tail: 0x53060601

Block header dump: 0x024005ef