Reuse of freed space in a cluster

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 - 03:49:15 CST