OK, I'll say it. Oracle does not always work the way I want it to work. The most obvious example of this is how indexes are managed. As data is manipulated, it is evident that the index does not reuse space that it had. For example, if I have a column containing the values A,D,B,E,C,F, and I put an index on this, then the index is created in the following order:

A,B,C,D,E,F.

This is part of what makes the index access so fast. So when I perform an update and change C to G, I will have the following:

A,B, ,D,E,F,G

The space in which the C was held is not reused. This actually is a good idea since it makes the update statement much faster than if a complete index rebuild was necessary for every update. The cost for this speed is empty holes in the index. Over time, it becomes evident that the index on the same number of rows slowly takes more space. To get this empty space back, you need to periodically rebuild an index.

Rebuild in the Same Tablespace

When you rebuild an index, you have the choice of rebuilding it in the same tablespace or not. Remember that the current index exists until the new one is successfully created. This can lead to fragmentation in the current tablespace that only worsens over time. An example of this is an index that was initially 256K with a next extent of 64K. If this index had been spread out to three extents, you could have the following:

Ext1(128k),other index,ext2(64k),other index, ext3(64k), other index

If you leave the index definition as it is, the rebuild will recreate the index in the first block that can hold 128k, resulting in:

Ext1(128k),other index,ext2(64k),other index, ext(64)3, other index,temporary(128k)
and then:

free(128k),other index,free(64k),other index, free(64)3, other
index,ext1(128k)

Now there is more free space mixed in with the indexes, and if the index grows and can’t fit in 128k anymore, you may end up with chunks of free space that are unusable.

No Fragment
To avoid this fragmentation, the common approach is to rebuild all of the indexes in the tablespace into another tablespace, coalesce this tablespace, and then rebuild them back. This means rebuilding the index twice when you want to do it once. The other option is simply to drop the indexes, coalesce the tablespace, then recreate. This will set any objects depending on this table to an invalid state and they will need to be recompiled. Depending on sizes, it is usually faster to rebuild.

8.1 to the Rescue
To avoid spending the time to rebuild, you should ensure that all extents in the tablespace are the same — “initial” is the same as “next” and all indexes have the same ... what? Then it doesn't matter if the tablespace becomes fragmented because all the space remains usable. If you are going to do this, you should also take advantage of the new locally managed tablespaces that Oracle provides in V.8.1.

First, create a tablespace and give it a uniform extent size:

Create tablespace local64k_idx
Datafile '…/local64k_idx01.dbf' size 512M
Autoextend on next 10M maxsize unlimited
Extent management local uniform size 64k;


Next, put the indexes in this tablespace and don’t worry about fragmentation.
Now before you start thinking, “finally, this guy wrote a short article,” here’s another important question: When should you decide to rebuild an index and reclaim the empty space within it? I usually say that an index that is in more than four extents should be rebuilt. And what if the index is really 1M? Should you rebuild it each time when a rebuild is not needed at all?

More Than One
You probably already know the answer to that question. You will simply have multiple tablespaces, each locally managed at different sizes. Since my tolerance is an index in four extents, I create one tablespace at an extent size of two blocks, one at eight, one at 32, and one at 128. See how this all falls into my four’s? If I have an 8k block size, then I create a 16k, 64k, 256k, 1M.
So where do you put what? Of course, you have to start with a guess. Go ahead and put them in whichever of the four extents you think is correct, and analyze all of them. The rebuild script will put each where it belongs.

What Goes Where
The idea of the rebuild is that any index that is between the extent size for this tablespace and the extent size for the next tablespace belongs in this tablespace. You should pull all of these indexes into this tablespace. So, we have the following:

Tablespace Extent Index Size
16K indexes less than 64k
64K indexes >= 64k and less than 256k
256K indexes >=256k and less than 1M
1M indexes >=1M

Break Points
So as not to be fooled by over-allocated indexes, you should check the leaf_blocks for the index instead of the bytes. This gives a true picture of space used instead of space allocated.
Assuming you have a block size of 8k, you should first find the number of blocks in 64k to use as your comparison point.

variable limit number
begin
select 65536/value into :limit
from v$parameter where name = 'db_block_size';
end;
/
print :limit

Script
Each tablespace will have its own script, but they are all basically the same, as indicated by the following:

select 'alter index '||owner||'.'||index_name||' rebuild' ||chr(10)
'tablespace local16k_idx'||
' nologging;'||chr(10)||
'analyze index '||owner||'.'||index_name||' compute statistics;'from
dba_indexes
where leaf_blocks < :limit
and owner not in ('SYS','SYSTEM')
and last_analyzed is not null
and partitioned= 'NO'
and tablespace_name != 'LOCAL16K_IDX';
spool off
@rebuild_local16.sql

For the other tablespaces, use the following where clauses:

64k: where leaf_blocks >= (:limit) and leaf_blocks < (4*:limit)
256k: where leaf_blocks >= (4*:limit) and leaf_blocks < (16*:limit)
1M: where leaf_blocks >= (16*:limit)

See the pattern?
Each tablespace will pull in all the indexes that belong in it. If you have partitioned indexes, just throw in a union with dba_ind_partitions.
Note that you are only analyzing indexes when you rebuild them. This entire approach depends on the index being analyzed the first time it is built so you have data with which to work.
Conclusion
Last month we talked about how to partition indexes when they become too big. You will see that indexes that are less than a level of three do not usually become bigger than 4M. If you do have indexes larger than 4M, you might also want to make a local 4m tablespace. Now you can rebuild just the indexes that have either spread out or truly grown, without having to worry about fragmentation in these tablespaces. What a relief!