Empty datablocks not reused when inserting in a clustered table

Dear very wise sirs, I have a big problem and would
be extremely grateful if you could help me.

We have a cluster with two clustered tables, as described
below. To our big surprise, despite daily purges, on site
databases grow slowly but continuously.

In one of these databases, the cluster contains approximately
58000 data blocs. Of these, only 20000 contains rows. The others
are fully empty.

I performed massive inserts and updates (no deletes)
in a single session. These statements consumed 10000 new blocks.
To my surprise, Oracle added an extent. Actually, 1765
blocks were added instead of being reused.

It would be very very helpful to me if

• Somebody could point me to why this happens and how I can ensure that all empty blocks are reused before a new extent is allocated.
• Somebody could explain or give a reference to some text which explains precisely how Oracle finds free space when it has to insert a new row in a clustered table.

Thanks very much in advance

• Raoul

Database is Personal oracle 7.3.4, block size is 8K,
default PCTUSED is 40, tablespace HIST_TABLE has initial extent = next extent = 10240K, percent increase 0. Cluster and tables (names changed) are :

CREATE CLUSTER TRCluster (R NUMBER) SIZE 16384   TABLESPACE HIST_TABLE INITRANS 3 STORAGE (FREELISTS 4) PCTFREE 20
;

CREATE INDEX TRCluster ON CLUSTER TRCluster TABLESPACE HIST_INDEX
;

CREATE TABLE TR
(
  R NUMBER NOT NULL,
  ...
)
CLUSTER TRCluster(R)
;

CREATE TABLE ATR
(
  R NUMBER NOT NULL DEFAULT 1,
)
CLUSTER TRCluster(R)
;

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 05 1999 - 07:50:28 CST