compresstable.sql

derby database source code.good for you.

-- tests for system procedure SYSCS_COMPRESS_TABLE
-- that reclaims disk space to the OS

run resource 'createTestProcedures.subsql';
maximumdisplaywidth 512;
CREATE FUNCTION ConsistencyChecker() RETURNS VARCHAR(128)
EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_ConsistencyChecker.runConsistencyChecker'
LANGUAGE JAVA PARAMETER STYLE JAVA;


-- create tables
create table noindexes(c1 int, c2 char(30), c3 decimal(5,2));
create table indexes(c1 int, c2 char(30), c3 decimal(5,2));
create index i_c1 on indexes(c1);
create index i_c2 on indexes(c2);
create index i_c3 on indexes(c3);
create index i_c3c1 on indexes(c3, c1);
create index i_c2c1 on indexes(c2, c1);

create table oldconglom(o_cnum bigint, o_cname long varchar);
create table newconglom(n_cnum bigint, n_cname long varchar);

create view v_noindexes as select * from noindexes;

autocommit off;

-- test with heap only
-- test with empty table
insert into oldconglom
select conglomeratenumber, conglomeratename 
from sys.systables t, sys.sysconglomerates c
where t.tablename = 'NOINDEXES' and t.tableid = c.tableid;
select count(*) from oldconglom;
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 0);
insert into newconglom
select conglomeratenumber, conglomeratename 
from sys.systables t, sys.sysconglomerates c
where t.tablename = 'NOINDEXES' and t.tableid = c.tableid;
select * from oldconglom, newconglom where o_cnum = n_cnum;
select count(*) from newconglom;
select * from noindexes;
-- do consistency check on scans, etc.
values ConsistencyChecker();
rollback;

-- test with various sizes as we use bulk fetch
insert into noindexes values (1, '1', 1.1), (2, '2', 2.2), (3, '3', 3.3),
     (4, '4', 4.4), (5, '5', 5.5), (6, '6', 6.6), (7, '7', 7.7);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 0);
select * from noindexes;
insert into noindexes values (8, '8', 8.8), (8, '8', 8.8), (9, '9', 9.9),
     (10, '10', 10.10), (11, '11', 11.11), (12, '12', 12.12), (13, '13', 13.13),
     (14, '14', 14.14), (15, '15', 15.15), (16, '16', 16.16);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 0);
select * from noindexes;
insert into noindexes values (17, '17', 17.17), (18, '18', 18.18);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 0);
select * from noindexes;
-- do consistency check on scans, etc.
values ConsistencyChecker();
rollback;

-- test with some indexes
-- test with empty table
insert into oldconglom
select conglomeratenumber, conglomeratename 
from sys.systables t, sys.sysconglomerates c
where t.tablename = 'INDEXES' and t.tableid = c.tableid;
select count(*) from oldconglom;
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 0);
insert into newconglom
select conglomeratenumber, conglomeratename 
from sys.systables t, sys.sysconglomerates c
where t.tablename = 'INDEXES' and t.tableid = c.tableid;
select * from oldconglom, newconglom where o_cnum = n_cnum;
select count(*) from newconglom;
select * from indexes;
-- do consistency check on scans, etc.
values ConsistencyChecker();
rollback;

-- test with various sizes as we use bulk fetch
insert into indexes values (1, '1', 1.1), (2, '2', 2.2), (3, '3', 3.3),
     (4, '4', 4.4), (5, '5', 5.5), (6, '6', 6.6), (7, '7', 7.7);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 0);
select * from indexes;
insert into indexes values (8, '8', 8.8), (8, '8', 8.8), (9, '9', 9.9),
     (10, '10', 10.10), (11, '11', 11.11), (12, '12', 12.12), (13, '13', 13.13),
     (14, '14', 14.14), (15, '15', 15.15), (16, '16', 16.16);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 0);
select * from indexes;
insert into indexes values (17, '17', 17.17), (18, '18', 18.18);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 0);
select * from indexes;
-- do consistency check on scans, etc.
values ConsistencyChecker();
rollback;

-- primary/foreign keys
create table p (c1 char(1), y int not null, c2 char(1) not null, x int not null, constraint pk primary key(x,y));
create table f (x int, t int, y int, constraint fk foreign key (x,y) references p);
insert into p values ('1', 1, '1', 1);
insert into f values (1, 1, 1), (1, 1, null);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'P', 0);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'F', 0);
insert into f values (1, 1, 1);
insert into f values (2, 2, 2);
insert into p values ('2', 2, '2', 2);
insert into f values (2, 2, 2);
-- do consistency check on scans, etc.
values ConsistencyChecker();
rollback;

-- self referencing table
create table pf (x int not null constraint p primary key, y int constraint f references pf);
insert into pf values (1,1), (2, 2);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'PF', 0);
insert into pf values (3,1), (4, 2);
insert into pf values (3,1);
insert into pf values (5,6);
-- do consistency check on scans, etc.
values ConsistencyChecker();
rollback;

-- multiple indexes on same column
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');
create table t (i int, s varchar(1500));
create index t_s on t(s);
create index t_si on t(s, i);
insert into t values (1, '1'), (2, '2');
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T', 0);
select * from t;
-- do consistency check on scans, etc.
values ConsistencyChecker();
rollback;

-- verify statements get re-prepared
create table t(c1 int, c2 int);
insert into t values (1, 2), (3, 4), (5, 6);
prepare p1 as 'select * from t where c2 = 4';
execute p1;
prepare s as 'select * from t where c2 = 6';
execute s;
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T', 0);
execute p1;
execute s;
remove p1;
remove s;
-- do consistency check on scans, etc.
values ConsistencyChecker();
rollback;

-- verify that space getting reclaimed
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');
create table t(c1 int, c2 varchar(1500));
insert into t values (1,PADSTRING('1', 1500)), (2,PADSTRING('2', 1500)), (3,PADSTRING('3', 1500)), (4, PADSTRING('4', 1500)),
	(5, PADSTRING('5', 1500)), (6, PADSTRING('6', 1500)), (7, PADSTRING('7', 1500)), (8, PADSTRING('8', 1500));
create table oldinfo (cname varchar(128), nap bigint);
insert into oldinfo select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('T') t;
delete from t where c1 in (1, 3, 5, 7);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T', 0);
create table newinfo (cname varchar(128), nap bigint);
insert into newinfo select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('T') t;
-- verify space reclaimed, this query should return 'compressed!'
-- if nothing is returned from this query, then the table was not compressed
select 'compressed!' from oldinfo o, newinfo n where o.cname = n.cname and o.nap > n.nap;
rollback;

-- sequential
-- no indexes
-- empty table
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 1);
select * from v_noindexes;
-- full table
insert into noindexes values (1, '1', 1.1), (2, '2', 2.2), (3, '3', 3.3),
     (4, '4', 4.4), (5, '5', 5.5), (6, '6', 6.6), (7, '7', 7.7);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 1);
select * from v_noindexes;
insert into noindexes values (8, '8', 8.8), (8, '8', 8.8), (9, '9', 9.9),
     (10, '10', 10.10), (11, '11', 11.11), (12, '12', 12.12), (13, '13', 13.13),
     (14, '14', 14.14), (15, '15', 15.15), (16, '16', 16.16);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 1);
select * from v_noindexes;
insert into noindexes values (17, '17', 17.17), (18, '18', 18.18);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 1);
select * from v_noindexes;
rollback;

-- 1 index
drop index i_c2;
drop index i_c3;
drop index i_c2c1;
drop index i_c3c1;
-- empty table
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
select * from indexes;
-- full table
insert into indexes values (1, '1', 1.1), (2, '2', 2.2), (3, '3', 3.3),
     (4, '4', 4.4), (5, '5', 5.5), (6, '6', 6.6), (7, '7', 7.7);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
select * from indexes;
insert into indexes values (8, '8', 8.8), (8, '8', 8.8), (9, '9', 9.9),
     (10, '10', 10.10), (11, '11', 11.11), (12, '12', 12.12), (13, '13', 13.13),
     (14, '14', 14.14), (15, '15', 15.15), (16, '16', 16.16);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
select * from indexes;
insert into indexes values (17, '17', 17.17), (18, '18', 18.18);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
select * from indexes;
rollback;

-- multiple indexes
-- empty table
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
select * from indexes;
-- full table
insert into indexes values (1, '1', 1.1), (2, '2', 2.2), (3, '3', 3.3),
     (4, '4', 4.4), (5, '5', 5.5), (6, '6', 6.6), (7, '7', 7.7);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
select * from indexes;
insert into indexes values (8, '8', 8.8), (8, '8', 8.8), (9, '9', 9.9),
     (10, '10', 10.10), (11, '11', 11.11), (12, '12', 12.12), (13, '13', 13.13),
     (14, '14', 14.14), (15, '15', 15.15), (16, '16', 16.16);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
select * from indexes;
insert into indexes values (17, '17', 17.17), (18, '18', 18.18);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
select * from indexes;
rollback;

--table with multiple indexes, indexes share columns
--table has more than 4 rows
-- multiple indexes on same column
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');
create table tab (a int, b int, s varchar(1500));
create index i_a on tab(a);
create index i_s on tab(s);
create index i_ab on tab(a, b);
insert into tab values (1, 1, 'abc'), (2, 2,  'bcd');
insert into tab values (3, 3, 'abc'), (4, 4,  'bcd');
insert into tab values (5, 5, 'abc'), (6, 6,  'bcd');
insert into tab values (7, 7, 'abc'), (8, 8,  'bcd');
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB', 1);
select * from tab;
-- do consistency check on scans, etc.
values ConsistencyChecker();
--record the number of rows