-
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?
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
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.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
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.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|