jump to navigation

Do ROWID Index Row Entry Columns Impact Index Block Splits ? December 20, 2007

Posted by Richard Foote in Concatenated Indexes, Index Block Splits, Index Internals, Oracle Indexes, Richard's Musings, ROWID.

Based on a great question by Alberto Dell’Era  in my “Differences Between Unique/Non-Unique” blog entry (comment 9), I thought it might be a useful exercise to show how I go about confirming my understanding of a specific concept by trying to develop a little test case or demo that can illustrate the concept. My “magic incarnation” if you like 😉

The basic question was does the ROWID that constitutes an additional column in a Non-Unique index determine whether a particular row entry is the maximum or equivalent entry or not. Because by implication, this can determine and influence whether Oracle performs the generally preferred 90-10 splits rather than 50-50 block splits for indexed column values that at least equal the maximum value.

The answer is yes because the ROWID column is just another column in the index row entry and is simply treated the same. But how to actually “illustrate” and show this ?

I needed a way therefore to insert a ROWID that was always going to be the maximum ROWID value for a Non-Unique index. Then insert a whole bunch of subsequent ROWIDs of a lesser value than the maximum and inspect via index statistics whether the type of block splits changed from 90-10 to 50-50 block splits. Remember with the Object Number being equal (if it’s there at all), the next significant portion of the ROWID is the Relative File Number.

The plan was (reasonably) simple. Create a tablespace with one data file and fill it with something. Then add a second data file and use this to store the start of my table of interest (and of course create the index). This will create a whole bunch of rows with ROWIDs of a higher Relative File Number than those in the first data file. Then drop the first table and ensure the second table uses the free space created in the first data file. That way, a whole bunch of ROWIDs can be created that are less than existing ROWIDs because it would be using ROWIDs from the first data file, which has a lesser Relative File Number.

It’s the usual process I go through with these things. Find something that’s of interest, have some idea on how I think things work, come up with plans or strategies that will illustrate whether or not what I think is true (ensuring that somewhere in the process I include at least one reference to David Bowie ;). I can then later take the initial strategies and expand them for all applicable database options and features. Then see if anything changes between database versions and platforms.

Hopefully this demo shows you how I went about proving this: Do ROWID Index Row Entry Columns Impact Index Block Splits Demo.

The benefit of then showing these demos is that others can see exactly how I came to a conclusion, potentially try them out for oneself and perhaps see holes or flaws or shortfalls in the strategy or expand or tailor them for individual requirements or environments.


1. Robert - December 20, 2007

> ensuring that somewhere in the process I include
> at least one reference to David Bowie

Now this explains why all of your images do look so familiar. And I thought you were twins… 🙂


PS: Please keep up the good work!


2. Richard Foote - December 20, 2007

Hi Robert

Ooops, secret’s out 😉

I’ll try and keep this blog going although the “distractions” at times makes one wonder if it’s actually all worth it …


3. Alberto Dell'Era - December 20, 2007

Thanks Richard, your illustration is convincing and crystal clear at the same time. It would have taken me “Five Years” (hint..hint 😉 ) to design such a test case – and the technique of adding a new datafile is interesting as well, reusable for other scenarios for sure. Thanks again.


4. Brian Tkatch - December 20, 2007

Very nice.


5. Richard Foote - December 21, 2007

Alberto, sometimes “It Ain’t Easy” !!

This pun is fully intended and will no doubt make all those who love Ziggy cringe a little 😉


6. Don Seiler - December 26, 2007

Hopefully this gets you some Fame from All the Young Dudes.

/me tries too hard.


7. Dax - May 10, 2012

Hi Richard,

Pl. Pl. can you resolve demo error.. I am not able to see any of your demo.. or can you pl. provide other link where demo are available? . Pl.


Richard Foote - May 11, 2012

Hi Dax

This is a long standing problem. Will try and find time to fix this one for you this weekend. It’s one of the reasons why I always include the demo now as part of the blog post itself.


8. ahmed aangour - December 28, 2013

Hi Richard,

As I’m still waiting (in vain) for a book on Oracle Indexes written by you, I decided (in order to enhance my understanding on Oracle indexes) to read all your articles from the begining. However, like Dax in the previous comment I cannot see any of your demo 😦
Is it possible to share/publish these demo via another way?
I would really appreciate if you could this.


Richard Foote - January 3, 2014

Hi Ahmed

Yes, I’m sorry that despite a number of offers in the past, I just haven’t had either the spare time or energy to write such a book. I consider this blog my “book” in that I’ve discussed much here that would be any book anyways 🙂

As to the demos, many of the older links do work, it’s just that a number point to plain text files that wordpress no longer supports and so need to converted to another format.

If there’s any post in particular you want fixed let me know, else I’ll slowing work my way through them.


Richard Foote - January 3, 2014

Hi Ahmed

OK, some good news. I’ve finally gone through all the older posts and fixed up all the formats to the demo links. They should all now be viewable by everyone.

If you happen to come across a link that doesn’t work just let me know.


Leave a Reply to Richard Foote Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: