db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Behaviour of SYSCS_COMPRESS_TABLE
Date Wed, 01 Jun 2005 17:27:51 GMT
The split description below describes Derby behavior.
Derby does not merge "almost" empty pages - only empty pages.
Derby maintains a separate file per table and a separate file
per index.  Derby maintains a free page pool per file and only
can use the free pages in the same file to fill space requests.



The Wogster wrote:
> Ø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 
> itself).
> 
> 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.
> 
> 
> W
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 


Mime
View raw message