-
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.
-
Amar
"There is a difference between knowing the path and walking the path."
-
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
-
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
-
-
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
-
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
-
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
-
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."
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|