Index Cluster space management

We have a billing application which stores transaction data against accounts in an index cluster. The cluster key is (ACCOUNT, SEQ) where ACCOUNT is the account number and SEQ is an integer which is incremented each month. This means that the index cluster allocates an new index entry and data blocks each month for each account which has transactions against it. This works fine for the performance of billing as the billing process work though each account and retrieves the transactions for the last month only.

The problem is that we are currently not able to reclaim space for old historical data. For example we want to archive/delete transaction data which is older than three month. We can delete the rows but we are not able to reclaim the empty data blocks or at least reuse them for new cluster key entries. So our hash cluster will go on growing forever even though the amount of data it stores remains constant due to our archiving.

Is there a solution to this problem? We tried rebuilding the cluster index hoping it would free space for index entries which no longer contain data. This did not seem to free up any blocks, but I expected this as Oracle states it will not free any space below the high watermark. But can't Oracle at least reuse the empty blocks for new cluster key entries?

I know the solution in the long term is to use an IOT so we can partition on SEQ and simply truncate old partitions following archiving, but our application vendor will not support IOT under a future release. Also the cluster table currently contains 5 billlion rows (almost 2Tb) so this is not so simple so change.

regards
Ciaran Received on Wed Aug 13 2003 - 22:24:30 CDT