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.
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!
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.
Reply With Quote
Bookmarks