index tablespace don't given free

[tomate]

hey there,
i have some tables in a tablespace a. All tables in the tablespace a have indexes in another tablespace b. Now i delete rows from the tables in the tablespace a. I see the allocated space in the tablespace a will give free but the space in the index tablespace will not given free.
What is wrong. I must make always a alter index xxxx rebuild online but this can't i do everywere were we have a db.
Is there a solution.

[jmodic]

Your question is somewhat confusing.

When you delete rows from tables, you can't see any changes regarding free space on tablespace level. You can gain additional free space in tablespace only if you drop or truncate table, not if you delete rows.

But yes, there is a difference between tables and indexes regarding reusability of space freed by deleted rows. While with tables you can control how soon free space in blocks due to row deletion can be reused (blocks placed on free list) via PCTUSED parameter, there is no "free list concept" with indexes (hence you cant specify PCTUSED with indexes). So if you delete large number of rows from your table, probably large amount of table blocks will be put on free list and will be used for subsequent inserts. So after mass deletes followed by new inserts your table might not grow in size at all. But on the other hand, the same operation might cause large growth of table indexes. Freed space in index blocks due to row deletes can not be reused with subsequent inserted rows. There is only one exception: if *all entries* from index block are removed the block will be reused for subsequent entries.

[hrishy]

Hi Jurij

Here we meet again.I have also faced athe similar problem as tom is facing.lemme explain the problem here.what tom and i want is this.

suppose i have a table called tabA and index on it called indexA1,indexA2,and indexA3 now when i delete large number of rows from tabA my indexes wont be effective so we want to write sort of a trigger which fires when rows of a tabA are deleted and runs alter index rebuild indexA1,indexA2......and so on.how would we do this..........if i cannot catch the triggering action how else would i catch this event ............

i know i can rebuild my indexes perodically but i wish i could do it whenever there is a large deletion on tables.

regards
hrishy

P.S:i hope i am not adding to the confusion