INPLACE_COMPRESS_TABLE query

[Mayuresh Nirhali <Ma...@Sun.COM>]

Hello,

I was looking at Derby-606 and was able to reproduce the bug.
I am working with a table of 30 million rows of the schema mentioned in 
the JIRA entry.

I have a query regarding the size of the table before and after 
INPLACE_COMPRESS_TABLE command.

After creating the table and inserting the 30 million records, a simple
'du -sk' shows the size as,
12008738        testdb

I exeucted INPLACE_COMPRESS_TABLE through ij and the size returned from 
the same command is,
12020239        testdb

Since, I have not performed any deletes, the reduction in the size is 
not expected. ofcourse, the testdb directory does not include derby.log.
so, I am wondering about the little increase in the size. Is it in the 
form of some other logs ? Can someone throw some light on this increase 
in size ??

I would also appreciate any additional information on 
INPLACE_COMPRESS_TABLE.

TIA
Mayuresh,

Re: INPLACE_COMPRESS_TABLE query

[Stanley Bradbury <St...@gmail.com>]

Mayuresh Nirhali wrote:
> Hello,
>
> I was looking at Derby-606 and was able to reproduce the bug.
> I am working with a table of 30 million rows of the schema mentioned 
> in the JIRA entry.
>
> I have a query regarding the size of the table before and after 
> INPLACE_COMPRESS_TABLE command.
>
> After creating the table and inserting the 30 million records, a simple
> 'du -sk' shows the size as,
> 12008738        testdb
>
> I exeucted INPLACE_COMPRESS_TABLE through ij and the size returned 
> from the same command is,
> 12020239        testdb
>
> Since, I have not performed any deletes, the reduction in the size is 
> not expected. ofcourse, the testdb directory does not include derby.log.
> so, I am wondering about the little increase in the size. Is it in the 
> form of some other logs ? Can someone throw some light on this 
> increase in size ??
>
> I would also appreciate any additional information on 
> INPLACE_COMPRESS_TABLE.
>
> TIA
> Mayuresh,
>
Hi -

I'm going to make a guess that the increase is because in the initial 
dataload some of the free space reserved in each page (probably index 
pages) was utilized as new records were added and that the compress 
restored the default free space percentage for those pages thus 
spreading the records out over more pages.  You can check my speculation 
by comparing the before and after sizes of the files for each table and 
index in the seg0 directory  to see which are growing.  Be sure to check 
that the number of before and after files are the same (that there are 
no d*.dat files waiting to be removed or log*.dat files that contain 
transaction information that need to be processed.).

HTH