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
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:
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
free(128k),other index,free(64k),other index, free(64)3, other
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.
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
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
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
select 65536/value into :limit
from v$parameter where name = 'db_block_size';
Each tablespace will have its own script, but they are all basically the same, as indicated by
select 'alter index '||owner||'.'||index_name||' rebuild' ||chr(10)
'analyze index '||owner||'.'||index_name||' compute statistics;'from
where leaf_blocks < :limit
and owner not in ('SYS','SYSTEM')
and last_analyzed is not null
and partitioned= 'NO'
and tablespace_name != 'LOCAL16K_IDX';
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
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!