Index tablespace increasing...does Alter indexes rebuild help to reduce it??
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Index tablespace increasing...does Alter indexes rebuild help to reduce it??

  1. #1
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456

    Index tablespace increasing...does Alter indexes rebuild help to reduce it??

    Hi guys,

    I have Index tablespace increasing about 100MB per mth....if I perform alter indexes rebuild on all the indexes does it help to reduce tabblespace consumation??

    any inputs appreciated.


  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    No.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    Hi adewi,

    Thank-u for ur reply...in that case do u know if there's anyway to reduce the growth of tablespace other than resizing the tablespace??

    any inputs appreciated

  4. #4
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,
    Yes it could be,
    if your application creates new rows and deletes 'old' ones then a rebuild will shrink the size.
    If you have bitmaped indexes then they shrink too because they grow due to dml like update-stmt.
    Orca

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by ngwh
    Hi adewi,

    Thank-u for ur reply...in that case do u know if there's anyway to reduce the growth of tablespace other than resizing the tablespace??

    any inputs appreciated
    I don't see this as a problem. As you said that the size increases by 100M every month, that itself indicates that lots of data inserts is going on. And growth of tablespace due to valid inserts (important records) is what a database is expected to have.

    Now the thing to watch here is are there lots of unused indexes.

    if yes, then drop them because having unused indexes can lead to unwanted growth in tablespace.

    And if you cannot avoid those lots of indexes then you need to rewrite your application querries in such a way that it least hampers your business logic and functionality and use mininimum indexes.

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    Aug 2000
    Posts
    236
    Yse, sure it will if there are a lot of deleted rows in the base table. In that case, there are holes in the index which can be compacted with a rebuild statement.

    In 9i you could do an online rebuild of the index.

    Nizar

  7. #7
    Join Date
    Aug 2000
    Posts
    236
    Right Amar on why the index is growing. Also there might be a lot of deletes which does not claim back tablespace usage.

    You couls also COALESCE option with ALTER INDEX if you so not want to rebuild it.

    Nizar

  8. #8
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    pardon me for being a greenhorn...if I may ask...how can I tell which indexes I should rebuild inorder to eliminate to pick out those that are eating up too much tablespace?

    thank-u

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by nabaig
    Yse, sure it will if there are a lot of deleted rows in the base table. In that case, there are holes in the index which can be compacted with a rebuild statement.

    In 9i you could do an online rebuild of the index.

    Nizar
    No use, they will grow again....More ever you need twice the space during rebuild (Old and the New will both be there during the rebuild)

    And if you do it online, you'll need additional space to hold the changes that are made during the rebuild.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  10. #10
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    Hi ,

    What is the different between using COALSE and REBUILD for Alter Index... ??

    anyone can enlighten?

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