Re: Index Cluster space management

Hi!

One way would be not to increment SEQ anymore, but recycle it. Let say you want to store 5 years of data in your tables, your min SEQ will be 1 and max will be 60. And you just update some kind of support table to state that seq 1 doesn't belong to january 99 any more, but represents jan 2004 now. It might be problematic, if you want to keep other, older than 5 years data, that relies on SEQ though. Also if you want to reduce *existing* disk usage, then this recycling only won't help - a one-time reorganization would be needed anyway.

Performancewise, the deletes will generate lot's of IO to you, it'd be much more efficient to use partitioning and just drop partitions when needed.

Tanel.

"Ciaran Harron" <harronc_at_attglobal.net> wrote in message news:3f3b01af_1_at_news1.prserv.net...
> 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 Thu Aug 14 2003 - 03:33:35 CDT