Re: Reuse of freed space in a cluster

Hi Dom,

Once a block has been added to the cluster key chain for a particular cluster key, it is there to stay until the cluster is truncated. There is good reason for this. Consider for example the following short cluster key chain:

    base block <-> chained block

If transaction A, having deleted all the rows from the chained block, were to attempt to return that block to the freelist, it would need to reset the key chain pointer in the base block. That change would need to be made in a recursive transaction for immediate visibility to concurrent transactions - otherwise transaction B could insert a row into the chained block, only to have the key chain broken when transaction A commits. However, if transaction A were to unlink the chained block in a recursive transaction, transaction B could then modify the cluster key chain, making it impossible for transaction A to roll back.

There is a potential solution that is used to address the equivalent problem in indexes - namely to add the block to the freelist, but not remove it from the key chain. The removal from the key chain would then be done in a recursive transaction when the block gets reused. However, this is not done in the case of clusters. Oracle have deemed the cost unwarranted given that clusters are intended to optimize joins on the cluster key, and that advantage is lost if you have virtually ANY chaining. So because you are not supposed to allow cluster keys to chain, Oracle does not allow for the possibility in its space management. (Unfortunately, there is plenty of cluster key chaining the data dictionary of most Oracle databases - but that's another story).

Anyway, the bottom line for you is that an index cluster is a bad physical design to choose. Instead, I would suggest that you consider making the child table an index-organized table, and just use a simple heap table for the master table.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/

-----Original Message-----
From: "Dom" <dom_at_toto.com>

hello,

We have databases running in 8.0.5 or 8.1.6 HP UX 10.20

there is a cluster on master_detail tables, so there is one row of the master table
per block, they are never deleted, but frequently updated, though their size seldom changes.
For each row in the master table, there are 1 to 600 rows in the detail table,
with an average number of 60 operations per client, and an average size of 30 bytes per row.
some of them are purged periodically.
The size of the block is 8192. The cluster size is 20 Go in 10 datafiles, initial is 500 Mo,
next is 100 Mo, Pctfree is 10, Pctused 90.

When using a cluster, our first goal is to keep the execution time of our programs linear with the increase of the master table. Our second goal is to anticipate the growth of the two tables and predict their size.
The index cluster could also become a hashed cluster.

So we built a small application to check the behaviour of a cluster on a small model, where we do insertions, deletions and new insertions.

The expected behaviour is :
when rows are purged in a block where both PCTFREE and PCTUSED are reached, the freed space is reused for later insertions under the same cluster key as soon as possible.

The real behaviour is :
- deleted rows are not physically deleted, but just marked-as-deleted,
(this policy of delaying the physical reordering of the whole block seems okay)
- new insertions are NOT made within the block with deleted rows,
but instead in the first free block if there is one available in the allocated extents.
- new insertions are made within the block with deleted rows,
which is thus physically reorganized, ONLY if there is no more free block available

Our interpretation is that ORACLE estimates that it is easier to chain a new free block into the list of chained blocks for this cluster key,
than reorganize a previously used block.

As a consequence, the sum of chained blocks used to store the cluster is far much more than necessary, and is likely to increase indefinitly (as long there are free blocks available), thus making ORACLE's implementation of cluster worthless.

The worth case is that there is always a new extent allocated by the growth of the master table and
so Oracle use these blocks to made the inserts even there is space freed by delete.

Could someone answer these questions :
- what is the policy of use of the free list in a cluster ?
(The blocks under PCTUSED cannot be added to the free list, because they are not
candidates for insertion of any cluster key)
- how to force ORACLE to reuse the freed space instead of chaining new
blocks ?
- how to force ORACLE to physically reorganize the block after a deletion of
many rows ?
(This would be done in the purge program, no concern about the time it takes.)

thanks Received on Mon Nov 13 2000 - 13:09:14 CST