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 <[EMAIL PROTECTED]> 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
