1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
ij> autocommit off;
ij> -- start with simple test, does the call work?
create table test1 (a int);
0 rows inserted/updated/deleted
ij> -- call SYSCS_UTIL.SYSCS_ONLINE_COMPRESS_TABLE('APP', 'TEST1');
-- expect failures schema/table does not exist
-- call SYSCS_UTIL.SYSCS_ONLINE_COMPRESS_TABLE(null, 'test2');
-- call SYSCS_UTIL.SYSCS_ONLINE_COMPRESS_TABLE('APP', 'test2');
-- non existent schema
-- call SYSCS_UTIL.SYSCS_ONLINE_COMPRESS_TABLE('doesnotexist', 'a');
-- cleanup
drop table test1;
0 rows inserted/updated/deleted
ij> -- load up a table, delete most of it's rows and then see what compress does.
create table test1 (keycol int, a char(250), b char(250), c char(250), d char(250));
0 rows inserted/updated/deleted
ij> insert into test1 values (1, 'a', 'b', 'c', 'd');
1 row inserted/updated/deleted
ij> insert into test1 (select keycol + 1, a, b, c, d from test1);
1 row inserted/updated/deleted
ij> insert into test1 (select keycol + 2, a, b, c, d from test1);
2 rows inserted/updated/deleted
ij> insert into test1 (select keycol + 4, a, b, c, d from test1);
4 rows inserted/updated/deleted
ij> insert into test1 (select keycol + 8, a, b, c, d from test1);
8 rows inserted/updated/deleted
ij> insert into test1 (select keycol + 16, a, b, c, d from test1);
16 rows inserted/updated/deleted
ij> insert into test1 (select keycol + 32, a, b, c, d from test1);
32 rows inserted/updated/deleted
ij> insert into test1 (select keycol + 64, a, b, c, d from test1);
64 rows inserted/updated/deleted
ij> insert into test1 (select keycol + 128, a, b, c, d from test1);
128 rows inserted/updated/deleted
ij> insert into test1 (select keycol + 256, a, b, c, d from test1);
256 rows inserted/updated/deleted
ij> create index test1_idx on test1(keycol);
0 rows inserted/updated/deleted
ij> commit;
ij> select
conglomeratename, isindex, numallocatedpages, numfreepages, pagesize,
estimspacesaving
from new org.apache.derby.diag.SpaceTable('TEST1') t
order by conglomeratename;
CONGLOMERATENAME |ISIND&|NUMALLOCATEDPAGES |NUMFREEPAGES |PAGESIZE |ESTIMSPACESAVING
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST1 |0 |171 |0 |4096 |0
TEST1_IDX |1 |4 |0 |4096 |0
ij> delete from test1 where keycol > 300;
212 rows inserted/updated/deleted
ij> commit;
ij> delete from test1 where keycol < 100;
99 rows inserted/updated/deleted
ij> commit;
ij> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'TEST1', 1, 0, 0);
0 rows inserted/updated/deleted
ij> select
conglomeratename, isindex, numallocatedpages, numfreepages, pagesize,
estimspacesaving
from new org.apache.derby.diag.SpaceTable('TEST1') t
order by conglomeratename;
CONGLOMERATENAME |ISIND&|NUMALLOCATEDPAGES |NUMFREEPAGES |PAGESIZE |ESTIMSPACESAVING
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST1 |0 |68 |103 |4096 |421888
TEST1_IDX |1 |4 |0 |4096 |0
ij> commit;
ij> -- call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'TEST1', 0, 1, 0);
select
conglomeratename, isindex, numallocatedpages, numfreepages, pagesize,
estimspacesaving
from new org.apache.derby.diag.SpaceTable('TEST1') t
order by conglomeratename;
CONGLOMERATENAME |ISIND&|NUMALLOCATEDPAGES |NUMFREEPAGES |PAGESIZE |ESTIMSPACESAVING
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST1 |0 |68 |103 |4096 |421888
TEST1_IDX |1 |4 |0 |4096 |0
ij> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'TEST1', 0, 0, 1);
0 rows inserted/updated/deleted
ij> select
conglomeratename, isindex, numallocatedpages, numfreepages, pagesize,
estimspacesaving
from new org.apache.derby.diag.SpaceTable('TEST1') t
order by conglomeratename;
CONGLOMERATENAME |ISIND&|NUMALLOCATEDPAGES |NUMFREEPAGES |PAGESIZE |ESTIMSPACESAVING
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST1 |0 |68 |32 |4096 |131072
TEST1_IDX |1 |4 |0 |4096 |0
ij>
|
