db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From The Wogster <wogste...@yahoo.ca>
Subject Re: Behaviour of SYSCS_COMPRESS_TABLE
Date Wed, 01 Jun 2005 13:49:21 GMT
Øystein Grøvlen wrote:
>>>>>>"MM" == Mike Matrigali <mikem_app@sbcglobal.net> writes:
>     MM> Note that cloudscape automatically reuses space from deleted rows when
>     MM> new rows are inserted into the table.  The main problem
>     MM> SYSCS_COMPRESS_TABLE is solving is if there are a number of deletes
>     MM> which will not be followed by a number of inserts.  The reuse of space
>     MM> is not as efficient as the compress table at it squeezes every last bit
>     MM> of free space out, and returns that space to the OS.
> Is this also true for B-tree indexes?  I would imagine that if you
> have a index on a monotocally increasing key (e.g., a timestamp) and
> where you regularly delete old records, there may be a lot of empty
> B-tree pages that will never be possible to reuse.

What happens in most databases. is that the database has a fixed page 
size, say 8K, when an index page is full, it splits that page into 2 
half pages.  When an index page is empty it's dropped from the index, 
and added to the empty page pool.  Many will merge almost empty 
neighbouring pages, but that doesn't matter for this discussion.

When a page is added, either an index page or a data page, and the EPP 
is empty, then the database adds a number of empty pages, and those are 
added to the empty page pool.  Now say you have a database and add 5 000 
000 entries, and get 5 entries per page, you now have ~1 000 000 pages. 
  Now you delete 4 999 999 records, you end up with 1 data page, one 
index page, and probably 1 header page, total of say 3 pages.  However 
there are now 4 999 996 pages in the empty page pool (typically a table 

When you compress the database it knows it only needs 3 pages, so it 
builds a new file of only 3 pages.  Most databases create a number of 
empty pages in addition to the 3 needed anyway.


View raw message