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

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 …