db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oystein Grovlen - Sun Norway <Oystein.Grov...@Sun.COM>
Subject Re: Does in-place compress really defragment?
Date Tue, 04 Apr 2006 13:47:27 GMT
Mike Matrigali wrote:

> I am most concerned about 7 to 8, that should reduce space assuming
> the 171 rows don't all fit on a page.

Does this mean that it is only space in completely empty pages that will 
be reused when defragmenting?

> 
> one question, what sizes of rows are you using (basically are there
> actually 300 characters in your "c" column), for quick tests I always
> use char columns as they use whatever space is declared no matter
> the data.  Varchar uses variable length storage.  

All rows have 200 character strings for the "c" column.

> It would be 
> interesting to throw in a select from the space table vti after every
> statement to see what is going on - see
> opensource/java/testing/org/apache/derbyTesting/functionTests/tests/store/SpaceTable.sql

> test for some usage.

OK, here is a replay of just step 7 and 8 including queries of the space 
table vti:

ij> create table t1 (i integer primary key, j integer, c varchar(300));
0 rows inserted/updated/deleted
ij> insert into t1 select * from t;
1536 rows inserted/updated/deleted
ij> select conglomeratename, isindex, numallocatedpages, numfreepages, 
pagesize, estimspacesaving from new 
org.apache.derby.diag.SpaceTable('T1') t order by conglomeratename;
CONGLOMERATENAME 
 
|ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE 
|ESTIMSPACESAVING
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL060404033237400 
                                                         |1     |10 
              |0                   |4096       |0
T1 
                                                         |0     |103 
              |0                   |4096       |0

2 rows selected
ij> delete from t1 where i < 512;
512 rows inserted/updated/deleted
ij> select conglomeratename, isindex, numallocatedpages, numfreepages, 
pagesize, estimspacesaving from new 
org.apache.derby.diag.SpaceTable('T1') t order by conglomeratename;
CONGLOMERATENAME 
 
|ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE 
|ESTIMSPACESAVING
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL060404033237400 
                                                         |1     |8 
              |2                   |4096       |8192
T1 
                                                         |0     |70 
              |33                  |4096       |135168

2 rows selected
ij> CALL SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'T1', 1, 1, 1);
0 rows inserted/updated/deleted
ij> select conglomeratename, isindex, numallocatedpages, numfreepages, 
pagesize, estimspacesaving from new 
org.apache.derby.diag.SpaceTable('T1') t order by conglomeratename;
CONGLOMERATENAME 
 
|ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE 
|ESTIMSPACESAVING
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL060404033237400 
                                                         |1     |8 
              |2                   |4096       |8192
T1 
                                                         |0     |103 
              |0                   |4096       |0

2 rows selected



Looks like compress reuses the empty pages without freeing other pages.

-- 
Øystein

Mime
View raw message