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

[ngwh]

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.

[adewri]

No.

[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

[Orca777]

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

[adewri]

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

[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

[nabaig]

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

[ngwh]

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

[adewri]

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.

[ngwh]

Hi ,

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

anyone can enlighten?