RE: When will Oracle reuse free space created with deletes

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 27 Aug 2004 13:00:54 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKEEHEFEAA.mwf@rsiz.com>

Okay. In the small number of rows example you've presented, you're likely working entirely within a single block. There is no guaranteed order of insertion in a block and no guaranteed order of use of blocks. In practice with the current implementations you're seeing Oracle find a space in the current insert block that sweetly fits the chunk you're shoving in, so it uses it.

You could get some additional information from http://integrid.info/Poder_Freelists_vs_ASSM.ppt

which Tanel Podel (with a cooler character set than I'm using that's spelled slightly differently) kindly posted a while ago in another thread on this list. It is an excellent presentation that has precision in terminology and the order of operations of how Oracle handles free space, and highlights the contrasts between freelist management and ASSM management.

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of ryan_gaffuri_at_comcast.net
Sent: Friday, August 27, 2004 10:02 AM
To: oracle-l_at_freelists.org
Subject: When will Oracle reuse free space created with deletes

Been playing around.... When will Oracle attempt to reuse the space freed up by deletes? Seems like Oracle tends to insert farther down the heap, even if there is free space at the head of the table? Please see below?
rgaffuri> create table x (y number);
Table created.
rgaffuri> insert into x values(1);
1 row created.
rgaffuri> insert into x values(2);
1 row created.
rgaffuri insert into x values (3);
1 row created.
rgaffuri> select y from x;

         1
         2
         3

rgaffuri> delete from x where y = 1;
1 row deleted.
rgaffuri> select y from x;

         2
         3

rgaffuri> insert into x values(4);
1 row created.
rgaffuri> select y from x;

         2
         3
         4

If I insert 5 next, in what case would I see select y from x
5
2
3
4

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Fri Aug 27 2004 - 13:12:10 CDT