Index tablespace growing so much faster than data tablespaces

For some reason, the index tablespaces for one table are growing so much faster than the data tablespaces. Why? Is there anything else I can do except adding more space? The table is about 100 gig.

Thanks!

Does the tablespace hold multiple indexes? Are they all growing fast? How many indexes are there?

The table is composited partitioned (range-hash). So they are many 9 sub partitioned local bitmap indexes in one of the tablespaces. There are 13 index tablespaces for this table, 4 of them growing very fast!

Thanks!

Too much insert-update-delete on those partitions?

Yes, there are insert and delete on the table every night.

Check the indexes for dead leaf blocks and branch levels. You may have to rebuild the indexes.

Quote:

---

Originally posted by simply_dba
Check the indexes for dead leaf blocks and branch levels. You may have to rebuild the indexes.

---

Given the recent thread on myths about rebuilding indexes please let us know if rebuilding indexes recovers significant space.

Quote:

---

Originally posted by roadwarriorDBA
Given the recent thread on myths about rebuilding indexes please let us know if rebuilding indexes recovers significant space.

---

This would be a special case, where insert/delete/update gets applied to a bitmap index -- they tend to grow large very quickly. Although the problem is not so bad in more recent versions, in 8i they grow enormously. Bitmap indexes aren't really designed for such things, especialy if you have concurrent modifications.

Actually, there are two columns indexed on this four column table. One is b-tree, one is bitmap. The insert and delete are the only two sequencial processes on the table. There are also both indexes in the tablespaces that are growing very fast.

If the column value on the b-tree index is monotonically increasing, then coalesce the index periodically.

I had the same experience few months ago. A 320 column table has 85 indexes and the table has 40 M rows and it's size 40 GB. Suddenly one index on a single date column had grown up to 8GB. The expected size is around 1.3 GB. This column is updated by the sysdate value. And heavy DML have been happening on the table 24x7. So the deleted space in the index leaf blocks were not reused effectively by oracle. I put a weekly cron job coalesces the index. After this the index size oscillates between 1.3 GB to 1.5GB.

Tamil

But index coalesce does not reduce the height of the index.If the said indexes have 5-6 branches index rebuild would be a better idea.

Rebuild or coalesced?

Then, I found following information for one of the tablespace that has been growing very fast:

select block_id, sum(bytes) from dba_free_space
where tablespace_name like 'XYZ'
group by block_id;

block_id sum(bytes)
562209 419430400

and
select total_bytes, bytes_coalesced, total_blocks, blocks_coalesced from dba_free_space_coalesced
where tablespace_name like 'XYZ';

total_bytes bytes_coalesced total_blocks blocks_coalesced
419430400 419430400 51200 51200


It seems to me system automatically coalesced the free space.

The branch level is only up to 3 from dba_ind_subpartitions.

The tablespaces are all locally managed and the initial=next, pctincrease=0. So it's not necessary to coalesce tablespaces.

The table is a composite partitioned table (rang_hash). If I rebuild the index, I have to rebuild at subpartition level, alter index rebuild subpartition xyz ...

Is there a command to coalesce index for this kind of table?

Quote:

---

Originally posted by lgaorcl

Is there a command to coalesce index for this kind of table?

---

Yes.
Alter Index INDEX_NAME Coalesce;

What I really like to know is: is there any command down to subpartition level like alter index ... rebuild subpartition ....? Because this is a composite partitioned table.

Quote:

---

A 320 column table has 85 indexes and the table has 40 M rows and it's size 40 GB

---

PeopleSoft perchance ;)

===
PeopleSoft perchance
===
No, it is Siebel.

Tamil