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
