Oystein Grovlen - Sun Norway wrote:
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.
This looks like a bug, I will report and am actively looking at it.
