jump to navigation

How To Rebuild And Make An Index Bigger, Not Smaller (Carry That Weight) January 13, 2009

Posted by Richard Foote in Index Shrink, Oracle Myths.
trackback

I sometimes hear suggestions along the lines of:

 “when you rebuild an index, at least you make the index as small and efficient as possible, even if it doesn’t necessarily improve performance”

or

“when you rebuild an index, at least you’ll always save some space and storage if nothing else”.

However, this of course is not necessarily the case. There are many scenarios where by rebuilding an index, you can actually make the index bigger, not smaller, you can make the index use more storage, not less storage, you can make the index less efficient, not more efficient and hence you can make the exercise worse than useless, not just useless.

It all depends.

Here are just a few little examples whereby rebuilding the index has resulted in a larger index than it was before the index was rebuilt. Note in all of these scenarios, I’ll be sticking with the default PCTFREE value of 10%, which is by far the most commonly set PCTFREE value of every Oracle index out there in existence.

The first example is very simple and so very common. It’s simply an index on a monotonically increasing value, as used by many Primary Keys out there in the “real world”. 

First we create the table and associated index. Nice and simple …

SQL> create table bowie (id number);

Table created.

SQL> create index bowie_i on bowie(id);

Index created.

We now populate the index with a whole bunch of monotonically increasing values …

SQL> insert into bowie select rownum from dual connect by level <=1000000;

1000000 rows created.

SQL> commit;

Commit complete

Let’s see how big our index might now be …

SQL> select blocks, lf_blks, btree_space, pct_used from index_stats;

BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------ ------- ----------- --------
  2048    1999    16024128      100

Note the index has a “perfect” PCT_USED value of 100%, you can’t get any better than that !! Why ? Because as the index is on a monotonically increasing value, all the inserts take place on the “right hand side” of the index and Oracle performs it’s 90-10 index block split operation, rather than the 50-50 block split. 

If we try and rebuild such an index …

SQL> alter index bowie_i rebuild;

Index altered.

SQL> analyze index bowie_i validate structure;

Index analyzed.

SQL> select blocks, lf_blks, btree_space, pct_used from index_stats;

BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------ ------- ----------- --------
  2304    2226    17848160       90

We notice that the index is now actually bigger, not smaller. Not only that, but we have now introduced 10% free space that will likely not be reused.

 

Another example.

This time we build a similar table but this time populate it with randomly generated numbers, not monotonically increasing values.

SQL> drop table bowie;

Table dropped.

SQL> create table bowie (id number);

Table created.

SQL> insert into bowie select ceil(dbms_random.value(0,100000)) from dual connect by level <=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

Let’s now create an index on this numeric column …

SQL> create index bowie_i on bowie(id);

Index created.

Let’s now add more random numbers to the table …

SQL> insert into bowie select ceil(dbms_random.value(0,100000)) from dual connect by level <=50000;

50000 rows created.

SQL> commit;

Commit complete.

OK, let’s check out the index …

SQL> analyze index bowie_i validate structure;

Index analyzed.

SQL> select blocks, lf_blks, btree_space, pct_used from index_stats;

BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------ ------- ----------- --------
  2304    2214    17760192       95

OK, the index has a PCT_USED value of 95%.

However, after the rebuild …

SQL> alter index bowie_i rebuild;

Index altered.

SQL> analyze index bowie_i validate structure;

Index analyzed.

SQL> select blocks, lf_blks, btree_space, pct_used from index_stats;

BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------ ------- ----------- --------
  2432    2324    18640192       90

The index is now bigger, with more leaf blocks and a worse PCT_USED value.

If we rebuild an index before the index has had the opportunity to use its available free space, an index rebuild can introduce more free space, not less. Now this may not necessarily be a bad thing, but it’s another example of the index potentially being bigger, not smaller after a rebuild.

 

Here’s yet another example.

Similar to the previous example, but this time we’ll throw in a whole bunch of update statements as well. As we know, an update statement within an index is effectively a delete statement, followed by an insert statement. All that “wasted” deleted space should be nicely cleaned up after an index rebuild, resulting in a smaller index structure. Or so we might think …

First, create and populate the table with randomly generated numbers similar to before …

SQL> drop table bowie;

Table dropped.

SQL> create table bowie (id number, value number);

Table created.

SQL> insert into bowie select rownum, ceil(dbms_random.value(0,100000)) from dual connect by level <=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

Now let’s create the index and aftewards insert a bunch of new rows as before.

SQL> create index bowie_i on bowie(value);

Index created.

SQL> insert into bowie select rownum+1000000, ceil(dbms_random.value(0,100000)) from dual connect by level <=50000;

50000 rows created.

SQL> commit;

Commit complete.

Now let’s run a little procedure that will update roughly 10% of all the data (a  reasonable overall percentage), with new random numbers.

SQL> begin
  2  for i in 1..105000 loop
  3    update bowie set value = ceil(dbms_random.value(0,100000))
  4    where id = i;
  5    commit;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

OK, let’s now look at the index, wasted space and all …

SQL> analyze index bowie_i validate structure;

Index analyzed.

SQL> select blocks, lf_blks, btree_space, pct_used from index_stats;

BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------ ------- ----------- --------
  2304    2234    17952320       94

Interestingly, even with all the random updates (and associated deletes), the index is relatively efficient at 2234 leaf blocks.

Let’s see how many of the 105,000 updated entries have remained deleted:

SQL> select del_lf_rows from index_stats;

DEL_LF_ROWS
-----------
       1129

Not many at all, just 1129 deleted entries remain, less than 1% !!

In fact, the vast majority of the deleted entries have all been automatically cleaned out by Oracle. Much more on the myth that an index with lots of update activity needs to be rebuilt to come in the near future …

Let’s rebuild this index now.

SQL> alter index bowie_i rebuild;

Index altered.

SQL> analyze index bowie_i validate structure;

Index analyzed.

SQL> select blocks, lf_blks, btree_space, pct_used from index_stats;

BLOCKS LF_BLKS BTREE_SPACE PCT_USED
------ ------- ----------- --------
  2432    2324    18640192       90

The index is again bigger than it was previously. Even after we’ve added in a bunch of rows and updated 10% of the entire table.

The point here of course is that even if an index is heavily updated, the deleted space can generally be subsequently reused. Like I said, more on all this in a future post.

For now however, just note that rebuilding an index doesn’t necessarily mean the resultant index will now be smaller or more efficient or more pristine. In many cases, rebuilding an index blindly can result in a larger index structure than it was prior to the rebuild.

Bear this in mind the next time you read or hear someone suggest an index rebuild will always ensure the new index is smaller and as efficient as possible …

About these ads

Comments»

1. Hemant K Chitale - January 14, 2009

The “myths” should be corrected.
The REBUILD OR COALESCE, as the case may be, *may* (not necessarily always) result in improvements (storage space, index_fast_full_scan, ability to load all index blocks in the buffer cache) if done *after* a SIGNFICANT DELETE (ie Purge) of rows. I would consider such improvements as criteria only for large Batch Jobs, not for OLTP queries.

I didn’t even know that there was a myth that “an index with signifcant update activity needs to be rebuilt”. Can be true only in some exceptional cases (eg updates have significantly shrunk the sizes of index keys), but can, in no way, be taken as a rule or guideline.

2. Dion Cho - January 14, 2009

Very interesting and insightful

3. Richard Foote - January 14, 2009

Hi Hemant

I’m trying to correct the myths, slowly but surely ;)

And yes, there really are people out there who think just because a table has had lots of update activity on an indexed column, the index should be regularly rebuilt. It all comes back to not understanding how Oracle indexes work :(

4. Surachart - January 15, 2009

that’s great idea…
Anyway When I should think to rebuild index…
when table’s deleted more > 10% , right?

5. Norman Dunbar - January 15, 2009

Hi Richard, long time no see/speak.

I’d hate to think what Don will have to say when/if he reads your latest posting on index rebuilds. It could be quite interesting – but don’t you go proving anything! ;-)

Keep up the good work on these indexes, I’m sort of gradually almost getting it!

Cheers,
Norm.

6. Richard Foote - January 16, 2009

Hi Surachart

10% deleted space ? Not 9% ;)

But what if you’re about to insert a whole bunch of new data and free up all or some of this deleted space ? By rebuilding the index, you may again introduce more so-called “wasted” space by incurring unnecessary 50-50 block splits earlier in the index life-cycle …

What if the index is only being used for either unique index scans or small index range scans and this deleted space doesn’t actually result in additional I/Os …

What if the index is really small (say 200 index entries only) and 10% doesn’t actual amount to much either way …

Yes, one doesn’t ordinarily delete > 10% of a table but even if you do, it still may not be enough to justify an index rebuild.

7. Richard Foote - January 16, 2009

Hi Norman

Hey, great to hear from you again, it really is !!

This blog is just my way of not having to continually repeat myself on the various Oracle forums ;)

Keep in touch !!

8. Norman Dunbar - January 16, 2009

Hi Richard,

“This blog is just my way of not having to continually repeat myself on the various Oracle forums”

Well good luck with that. The number of times I answer people who ask “should I rebuild my indexes” is huge, and growing.

Of course, I always tell them that rebuilding will improve space usage, efficiency and performance, not! ;-)

Cheers,
Norm.

9. Surachart - January 16, 2009

Hi Richard,

great! idea… that make me want to know more about index. I have to find more time read and test… about it.

Thank You.
Surachart

10. Richard Foote - January 16, 2009

Hi Norman

The tide is certainly turning regarding the general understanding of why blindly periodically rebuilding indexes may not be the smartest of moves.

However, with the ever growing size and number of databases combined with the ever shrinking maintanence windows and increasing availability requirements, many don’t really have a choice in the matter and need to spend their time and resources “wisely” doing activities that actually do make a difference to their businesses.

As Roger Waters once sang, “The Tide Is Turning”. It is indeed :)

11. Richard Foote - January 16, 2009

Hi Surachart

You need basically 3 things with regard to rebuilding indexes:

1) Know how indexes actually work and maintain themselves (or don’t as the odd case may be)

2) Know your data and

3) Know your applications and data processes

so you can determine and recognise when you may actually encounter a real scenario when an index has become problematic and either a one-off or periodic rebuild is warranted.

That’s it, just know those 3 things … ;)

12. Log Buffer #131: a Carnival of the Vanities for DBAs - January 17, 2009

[…] else’. However, this of course is not necessarily the case. There are many scenarios where by rebuilding an index, you can actually make the index bigger, not smaller . . .  It all depends.” Click through for Richard’s examples […]

13. Brian Tkatch - February 5, 2009

“Bare this in mind the next time you read or hear someone suggest”

I think you meant “bear”. To bare it in mind would be to open it for everyone to see, which might be scary and informing, but not quite what you meant. :)

14. Richard Foote - February 5, 2009

Thanks Brian, now fixed. Must have had a visit to a nude beach in mind when I wrote it ;)

15. Nigel - October 14, 2009

An excellent and revealing article. Thank you for sharing your knowledge.

A question: Rebuilding an index is sometimes necessary (a table had to be moved/whatever). So, if an index IS to be rebuilt, is there any way of preventing it flabbing-out?

Richard Foote - October 15, 2009

Hi Nigel

The most obvious way is to not add any more data to the table :)

Depends what you mean by “flabbing” out. If you mean, how can you prevent subsequent 50-50 block splits that introduces “empty index flab”, then one method is to introduce enough initial flab such that the index doesn’t need to introduce any more flab for a significant period of time.

However, the key point is some flab is OK, it’s actually a healthy aspect of an index because it means there’s enough reserve in the index for it to have additional entries inserted before it needs to worry about somewhat expensive block-split operations.

The flab in most cases will eventually be consumed anyways so let it be a happy and have a bit of flab to play with.

A randomly inserted index is not designed to be Twiggy, but is much more like a killer whale which can still perform its functions in an efficient manner, but needs its blabber to remain relatively healthy :)

16. Alan Mckeown - October 14, 2009

Hello Richard,
A very ineteresting article on indexes. I have seen this today on our test system that indexes can be larger after a rebuild. Coalescing looks interesting though.

regards

Alan

Richard Foote - October 15, 2009

Hi Alan

When discussing problematic indexes, the suggested fix is generally to rebuild the index. However, in such cases, coalescing is actually likely to be a more efficient, less resource intensive, less locking solution and fix than the rebuild.

Remember, one can always coalesce part of an index, as Oracle will simply ignore leaf blocks that are already packed enough. However, Oracle can’t rebuild part of an index, it’s all or nothing, in which case a rebuild will likely rebuild large portions of an index for no benefit, if large portions of an index were OK to begin with.

The default should be to not rebuild an index.

If however, one does encounter a problematic index, the default consideration should be to coalesce rather than rebuild to address the issue.

17. Log Buffer #166: a Carnival of the Vanities for DBAs | Pythian Group Blog - October 24, 2009

[…] in Oracle 11gR2. And still on typical DBAs’ tasks, we also have articles by Richard Foote: How To Rebuild And Make An Index Bigger, Not Smaller (Carry That Weight), Miladin Modrakovic’s Blocking locks history, Marco Gralike’s HOWTO: Partition Binary […]

18. Adam - March 4, 2011

Hi Richard,

In the first example, before we look for info from index_stats, is there not a missing command:

analyze index bowie_i validate structure;

?

Regards,

Adam

Richard Foote - April 20, 2011

Hi Adam

Yes, to make sure the reader was paying attention ;)

jagdeepsangwan - April 17, 2014

Then i think i was paying the attention while reading this, as i also noticed the same ;)

Regards,
Jagdeep Sangwan


Leave a 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,918 other followers

%d bloggers like this: