RE: Index Cluster space management

Hi Ciaran,

Just a few comments on how free space is managed in an index cluster.

The key row pieces have two fields appended to the row header which maintain (1) the current number of data rows in the block that reference this key, and (2) the pending number of rows should all active transactions commit. Both values are incremented on insert. The pending count is decremented on delete. The current count is corrected during cleanout operations.

When the previous value of the current count was zero, or the pending count is zero, then subject to whether active transactions commit or rollback, the key might have no dependent data row pieces in the block. In this case a flag is set in the data layer header of the block to indicate that there are potentially flushable keys. During block cleanout genuinely flushable keys (with no dependent data row pieces) are removed from the block, the space is freed and the key count is reduced so that another key can be inserted into that block.

Of course, a block with few enough keys and enough space to accept another key insert has to get back on a freelist before it will indeed be used for a subsequent key insert. At this point you just have to make sure that PCTUSED is set to a sensible value, as with any other heap segment.

So the bottom line is that other than the way in which flushable keys are tracked, there is nothing special about the way free space is managed in an index cluster as opposed to normal (table) heap segments. There is nothing stopping free space from being reused. (The above also applies to the non-fixed area blocks of a hash cluster).

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/         - For DBAs
@   http://www.christianity.net.au/  - For all 

"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 - 06:46:24 CDT