Index tablespace growing so much faster than data tablespaces
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Index tablespace growing so much faster than data tablespaces

  1. #1
    Join Date
    Jun 2000
    Posts
    315

    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!

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Does the tablespace hold multiple indexes? Are they all growing fast? How many indexes are there?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jun 2000
    Posts
    315
    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!

  4. #4
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

  5. #5
    Join Date
    Jun 2000
    Posts
    315
    Yes, there are insert and delete on the table every night.

  6. #6
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

  7. #7
    Join Date
    May 2005
    Posts
    129
    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.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Jun 2000
    Posts
    315
    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.

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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
  •  



Click Here to Expand Forum to Full Width