Google Git
Sign in
apache / derby / 08cc0f89fe077234078430bf9b93ebc9dfd70ac5 / . / java / testing / org / apache / derbyTesting / functionTests / master / compressTable.out
blob: edd180d853028a235cb7b0e5d5b013570c685c13 [file] [log] [blame]
ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
-- tests for system procedure SYSCS_COMPRESS_TABLE
-- that reclaims disk space to the OS
run resource 'createTestProcedures.subsql';
ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
CREATE FUNCTION PADSTRING (DATA VARCHAR(32000), LENGTH INTEGER) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Formatters.padString' LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_Access.waitForPostCommitToFinish' PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 512;
ij> CREATE FUNCTION ConsistencyChecker() RETURNS VARCHAR(128)
EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_ConsistencyChecker.runConsistencyChecker'
LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> -- create tables
create table noindexes(c1 int, c2 char(30), c3 decimal(5,2));
0 rows inserted/updated/deleted
ij> create table indexes(c1 int, c2 char(30), c3 decimal(5,2));
0 rows inserted/updated/deleted
ij> create index i_c1 on indexes(c1);
0 rows inserted/updated/deleted
ij> create index i_c2 on indexes(c2);
0 rows inserted/updated/deleted
ij> create index i_c3 on indexes(c3);
0 rows inserted/updated/deleted
ij> create index i_c3c1 on indexes(c3, c1);
0 rows inserted/updated/deleted
ij> create index i_c2c1 on indexes(c2, c1);
0 rows inserted/updated/deleted
ij> create table oldconglom(o_cnum bigint, o_cname long varchar);
0 rows inserted/updated/deleted
ij> create table newconglom(n_cnum bigint, n_cname long varchar);
0 rows inserted/updated/deleted
ij> create view v_noindexes as select * from noindexes;
0 rows inserted/updated/deleted
ij> autocommit off;
ij> -- 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;
1 row inserted/updated/deleted
ij> select count(*) from oldconglom;
1
-----------
1
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 0);
0 rows inserted/updated/deleted
ij> insert into newconglom
select conglomeratenumber, conglomeratename
from sys.systables t, sys.sysconglomerates c
where t.tablename = 'NOINDEXES' and t.tableid = c.tableid;
1 row inserted/updated/deleted
ij> select * from oldconglom, newconglom where o_cnum = n_cnum;
O_CNUM |O_CNAME |N_CNUM |N_CNAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij> select count(*) from newconglom;
1
-----------
1
ij> select * from noindexes;
C1 |C2 |C3
--------------------------------------------------
ij> -- do consistency check on scans, etc.
values ConsistencyChecker();
1
--------------------------------------------------------------------------------------------------------------------------------
No open scans, etc.
3 dependencies found
ij> rollback;
ij> -- 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);
7 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 0);
0 rows inserted/updated/deleted
ij> select * from noindexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
ij> 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);
10 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 0);
0 rows inserted/updated/deleted
ij> select * from noindexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
8 |8 |8.80
8 |8 |8.80
9 |9 |9.90
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
ij> insert into noindexes values (17, '17', 17.17), (18, '18', 18.18);
2 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 0);
0 rows inserted/updated/deleted
ij> select * from noindexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
8 |8 |8.80
8 |8 |8.80
9 |9 |9.90
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
17 |17 |17.17
18 |18 |18.18
ij> -- do consistency check on scans, etc.
values ConsistencyChecker();
1
--------------------------------------------------------------------------------------------------------------------------------
No open scans, etc.
3 dependencies found
ij> rollback;
ij> -- 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;
6 rows inserted/updated/deleted
ij> select count(*) from oldconglom;
1
-----------
6
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 0);
0 rows inserted/updated/deleted
ij> insert into newconglom
select conglomeratenumber, conglomeratename
from sys.systables t, sys.sysconglomerates c
where t.tablename = 'INDEXES' and t.tableid = c.tableid;
6 rows inserted/updated/deleted
ij> select * from oldconglom, newconglom where o_cnum = n_cnum;
O_CNUM |O_CNAME |N_CNUM |N_CNAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij> select count(*) from newconglom;
1
-----------
6
ij> select * from indexes;
C1 |C2 |C3
--------------------------------------------------
ij> -- do consistency check on scans, etc.
values ConsistencyChecker();
1
--------------------------------------------------------------------------------------------------------------------------------
No open scans, etc.
3 dependencies found
ij> rollback;
ij> -- 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);
7 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 0);
0 rows inserted/updated/deleted
ij> select * from indexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
ij> 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);
10 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 0);
0 rows inserted/updated/deleted
ij> select * from indexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
8 |8 |8.80
8 |8 |8.80
9 |9 |9.90
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
ij> insert into indexes values (17, '17', 17.17), (18, '18', 18.18);
2 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 0);
0 rows inserted/updated/deleted
ij> select * from indexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
8 |8 |8.80
8 |8 |8.80
9 |9 |9.90
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
17 |17 |17.17
18 |18 |18.18
ij> -- do consistency check on scans, etc.
values ConsistencyChecker();
1
--------------------------------------------------------------------------------------------------------------------------------
No open scans, etc.
3 dependencies found
ij> rollback;
ij> -- 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));
0 rows inserted/updated/deleted
ij> create table f (x int, t int, y int, constraint fk foreign key (x,y) references p);
0 rows inserted/updated/deleted
ij> insert into p values ('1', 1, '1', 1);
1 row inserted/updated/deleted
ij> insert into f values (1, 1, 1), (1, 1, null);
2 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'P', 0);
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'F', 0);
0 rows inserted/updated/deleted
ij> insert into f values (1, 1, 1);
1 row inserted/updated/deleted
ij> insert into f values (2, 2, 2);
ERROR 23503: INSERT on table 'F' caused a violation of foreign key constraint 'FK' for key (2,2). The statement has been rolled back.
ij> insert into p values ('2', 2, '2', 2);
1 row inserted/updated/deleted
ij> insert into f values (2, 2, 2);
1 row inserted/updated/deleted
ij> -- do consistency check on scans, etc.
values ConsistencyChecker();
1
--------------------------------------------------------------------------------------------------------------------------------
No open scans, etc.
4 dependencies found
ij> rollback;
ij> -- self referencing table
create table pf (x int not null constraint p primary key, y int constraint f references pf);
0 rows inserted/updated/deleted
ij> insert into pf values (1,1), (2, 2);
2 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'PF', 0);
0 rows inserted/updated/deleted
ij> insert into pf values (3,1), (4, 2);
2 rows inserted/updated/deleted
ij> insert into pf values (3,1);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'P' defined on 'PF'.
ij> insert into pf values (5,6);
ERROR 23503: INSERT on table 'PF' caused a violation of foreign key constraint 'F' for key (6). The statement has been rolled back.
ij> -- do consistency check on scans, etc.
values ConsistencyChecker();
1
--------------------------------------------------------------------------------------------------------------------------------
No open scans, etc.
4 dependencies found
ij> rollback;
ij> -- multiple indexes on same column
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');
0 rows inserted/updated/deleted
ij> create table t (i int, s varchar(1500));
0 rows inserted/updated/deleted
ij> create index t_s on t(s);
0 rows inserted/updated/deleted
ij> create index t_si on t(s, i);
0 rows inserted/updated/deleted
ij> insert into t values (1, '1'), (2, '2');
2 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T', 0);
0 rows inserted/updated/deleted
ij> select * from t;
I |S
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 |1
2 |2
ij> -- do consistency check on scans, etc.
values ConsistencyChecker();
1
--------------------------------------------------------------------------------------------------------------------------------
No open scans, etc.
3 dependencies found
ij> rollback;
ij> -- verify statements get re-prepared
create table t(c1 int, c2 int);
0 rows inserted/updated/deleted
ij> insert into t values (1, 2), (3, 4), (5, 6);
3 rows inserted/updated/deleted
ij> prepare p1 as 'select * from t where c2 = 4';
ij> execute p1;
C1 |C2
-----------------------
3 |4
ij> prepare s as 'select * from t where c2 = 6';
ij> execute s;
C1 |C2
-----------------------
5 |6
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T', 0);
0 rows inserted/updated/deleted
ij> execute p1;
C1 |C2
-----------------------
3 |4
ij> execute s;
C1 |C2
-----------------------
5 |6
ij> remove p1;
ij> remove s;
ij> -- do consistency check on scans, etc.
values ConsistencyChecker();
1
--------------------------------------------------------------------------------------------------------------------------------
No open scans, etc.
3 dependencies found
ij> rollback;
ij> -- verify that space getting reclaimed
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');
0 rows inserted/updated/deleted
ij> create table t(c1 int, c2 varchar(1500));
0 rows inserted/updated/deleted
ij> 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));
8 rows inserted/updated/deleted
ij> create table oldinfo (cname varchar(128), nap bigint);
0 rows inserted/updated/deleted
ij> insert into oldinfo select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('T') t;
1 row inserted/updated/deleted
ij> delete from t where c1 in (1, 3, 5, 7);
4 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T', 0);
0 rows inserted/updated/deleted
ij> create table newinfo (cname varchar(128), nap bigint);
0 rows inserted/updated/deleted
ij> insert into newinfo select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('T') t;
1 row inserted/updated/deleted
ij> -- 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;
1
-----------
compressed!
ij> rollback;
ij> -- sequential
-- no indexes
-- empty table
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 1);
0 rows inserted/updated/deleted
ij> select * from v_noindexes;
C1 |C2 |C3
--------------------------------------------------
ij> -- 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);
7 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 1);
0 rows inserted/updated/deleted
ij> select * from v_noindexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
ij> 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);
10 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 1);
0 rows inserted/updated/deleted
ij> select * from v_noindexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
8 |8 |8.80
8 |8 |8.80
9 |9 |9.90
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
ij> insert into noindexes values (17, '17', 17.17), (18, '18', 18.18);
2 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 1);
0 rows inserted/updated/deleted
ij> select * from v_noindexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
8 |8 |8.80
8 |8 |8.80
9 |9 |9.90
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
17 |17 |17.17
18 |18 |18.18
ij> rollback;
ij> -- 1 index
drop index i_c2;
0 rows inserted/updated/deleted
ij> drop index i_c3;
0 rows inserted/updated/deleted
ij> drop index i_c2c1;
0 rows inserted/updated/deleted
ij> drop index i_c3c1;
0 rows inserted/updated/deleted
ij> -- empty table
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
0 rows inserted/updated/deleted
ij> select * from indexes;
C1 |C2 |C3
--------------------------------------------------
ij> -- 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);
7 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
0 rows inserted/updated/deleted
ij> select * from indexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
ij> 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);
10 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
0 rows inserted/updated/deleted
ij> select * from indexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
8 |8 |8.80
8 |8 |8.80
9 |9 |9.90
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
ij> insert into indexes values (17, '17', 17.17), (18, '18', 18.18);
2 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
0 rows inserted/updated/deleted
ij> select * from indexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
8 |8 |8.80
8 |8 |8.80
9 |9 |9.90
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
17 |17 |17.17
18 |18 |18.18
ij> rollback;
ij> -- multiple indexes
-- empty table
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
0 rows inserted/updated/deleted
ij> select * from indexes;
C1 |C2 |C3
--------------------------------------------------
ij> -- 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);
7 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
0 rows inserted/updated/deleted
ij> select * from indexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
ij> 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);
10 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
0 rows inserted/updated/deleted
ij> select * from indexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
8 |8 |8.80
8 |8 |8.80
9 |9 |9.90
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
ij> insert into indexes values (17, '17', 17.17), (18, '18', 18.18);
2 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);
0 rows inserted/updated/deleted
ij> select * from indexes;
C1 |C2 |C3
--------------------------------------------------
1 |1 |1.10
2 |2 |2.20
3 |3 |3.30
4 |4 |4.40
5 |5 |5.50
6 |6 |6.60
7 |7 |7.70
8 |8 |8.80
8 |8 |8.80
9 |9 |9.90
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
17 |17 |17.17
18 |18 |18.18
ij> rollback;
ij> --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');
0 rows inserted/updated/deleted
ij> create table tab (a int, b int, s varchar(1500));
0 rows inserted/updated/deleted
ij> create index i_a on tab(a);
0 rows inserted/updated/deleted
ij> create index i_s on tab(s);
0 rows inserted/updated/deleted
ij> create index i_ab on tab(a, b);
0 rows inserted/updated/deleted
ij> insert into tab values (1, 1, 'abc'), (2, 2, 'bcd');
2 rows inserted/updated/deleted
ij> insert into tab values (3, 3, 'abc'), (4, 4, 'bcd');
2 rows inserted/updated/deleted
ij> insert into tab values (5, 5, 'abc'), (6, 6, 'bcd');
2 rows inserted/updated/deleted
ij> insert into tab values (7, 7, 'abc'), (8, 8, 'bcd');
2 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB', 1);
0 rows inserted/updated/deleted
ij> select * from tab;
A |B |S
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 |1 |abc
2 |2 |bcd
3 |3 |abc
4 |4 |bcd
5 |5 |abc
6 |6 |bcd
7 |7 |abc
8 |8 |bcd
ij> -- do consistency check on scans, etc.
values ConsistencyChecker();
1
--------------------------------------------------------------------------------------------------------------------------------
No open scans, etc.
3 dependencies found
ij> --record the number of rows
create table oldstat(rowCount int);
0 rows inserted/updated/deleted
ij> insert into oldstat select count(*) from tab;
1 row inserted/updated/deleted
ij> commit;
ij> --double the size of the table
select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('TAB') tab;
CONGLOMERATENAME |NUMALLOCATEDPAGES
-----------------------------------------------------------------------------------------------------------------------------------------------------
TAB |2
I_A |1
I_S |1
I_AB |1
ij> insert into tab values (1, 1, 'abc'), (2, 2, 'bcd');
2 rows inserted/updated/deleted
ij> insert into tab values (3, 3, 'abc'), (4, 4, 'bcd');
2 rows inserted/updated/deleted
ij> insert into tab values (5, 5, 'abc'), (6, 6, 'bcd');
2 rows inserted/updated/deleted
ij> insert into tab values (7, 7, 'abc'), (8, 8, 'bcd');
2 rows inserted/updated/deleted
ij> select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('TAB') tab;
CONGLOMERATENAME |NUMALLOCATEDPAGES
-----------------------------------------------------------------------------------------------------------------------------------------------------
TAB |2
I_A |1
I_S |1
I_AB |1
ij> delete from tab;
16 rows inserted/updated/deleted
ij> select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('TAB') tab;
CONGLOMERATENAME |NUMALLOCATEDPAGES
-----------------------------------------------------------------------------------------------------------------------------------------------------
TAB |2
I_A |1
I_S |1
I_AB |1
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB', 0);
0 rows inserted/updated/deleted
ij> -- verify space reclaimed
select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('TAB') tab;
CONGLOMERATENAME |NUMALLOCATEDPAGES
-----------------------------------------------------------------------------------------------------------------------------------------------------
TAB |2
I_A |1
I_S |1
I_AB |1
ij> -- do consistency check on scans, etc.
values ConsistencyChecker();
1
--------------------------------------------------------------------------------------------------------------------------------
No open scans, etc.
3 dependencies found
ij> rollback;
ij> --record the number of rows
create table newstat(rowCount int);
0 rows inserted/updated/deleted
ij> insert into newstat select count(*) from tab;
1 row inserted/updated/deleted
ij> --make sure the number of rows are the same
select o.rowCount, n.rowCount from oldstat o, newstat n where o.rowCount = n.rowCount;
ROWCOUNT |ROWCOUNT
-----------------------
8 |8
ij> --show old space usage
select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('TAB') tab;
CONGLOMERATENAME |NUMALLOCATEDPAGES
-----------------------------------------------------------------------------------------------------------------------------------------------------
TAB |2
I_A |1
I_S |1
I_AB |1
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB', 0);
0 rows inserted/updated/deleted
ij> --show new space usage
select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('TAB') tab;
CONGLOMERATENAME |NUMALLOCATEDPAGES
-----------------------------------------------------------------------------------------------------------------------------------------------------
TAB |2
I_A |1
I_S |1
I_AB |1
ij> rollback;
ij> drop table tab;
0 rows inserted/updated/deleted
ij> drop table oldstat;
0 rows inserted/updated/deleted
ij> -- test that many levels of aborts of compress table still work
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');
0 rows inserted/updated/deleted
ij> create table xena (a int, b int, c varchar(1000), d varchar(8000));
0 rows inserted/updated/deleted
ij> create index xena_idx1 on xena (a, c);
0 rows inserted/updated/deleted
ij> create unique index xena_idx2 on xena (b, c);
0 rows inserted/updated/deleted
ij> insert into xena values (1, 1, 'argo', 'horse');
1 row inserted/updated/deleted
ij> insert into xena values (1, -1, 'argo', 'horse');
1 row inserted/updated/deleted
ij> insert into xena values (2, 2, 'ares', 'god of war');
1 row inserted/updated/deleted
ij> insert into xena values (2, -2, 'ares', 'god of war');
1 row inserted/updated/deleted
ij> insert into xena values (3, 3, 'joxer', 'the mighty');
1 row inserted/updated/deleted
ij> insert into xena values (4, -4, 'gabrielle', 'side kick');
1 row inserted/updated/deleted
ij> insert into xena values (4, 4, 'gabrielle', 'side kick');
1 row inserted/updated/deleted
ij> select
conglomeratename, isindex,
numallocatedpages, numfreepages,
pagesize, estimspacesaving
from new org.apache.derby.diag.SpaceTable('XENA') t
order by conglomeratename;
CONGLOMERATENAME |ISIND&|NUMALLOCATEDPAGES |NUMFREEPAGES |PAGESIZE |ESTIMSPACESAVING
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
XENA |0 |1 |0 |4096 |0
XENA_IDX1 |1 |1 |0 |4096 |0
XENA_IDX2 |1 |1 |0 |4096 |0
ij> commit;
ij> delete from xena where b = 1;
1 row inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
0 rows inserted/updated/deleted
ij> select
cast (conglomeratename as char(10)) as name,
cast (numallocatedpages as char(4)) as aloc,
cast (numfreepages as char(4)) as free,
cast (estimspacesaving as char(10)) as est
from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
NAME |ALOC|FREE|EST
-------------------------------
XENA |2 |0 |0
XENA_IDX1 |1 |0 |0
XENA_IDX2 |1 |0 |0
ij> create table xena2(a int);
0 rows inserted/updated/deleted
ij> delete from xena where b = 2;
1 row inserted/updated/deleted
ij> select
cast (conglomeratename as char(10)) as name,
cast (numallocatedpages as char(4)) as aloc,
cast (numfreepages as char(4)) as free,
cast (estimspacesaving as char(10)) as est
from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
NAME |ALOC|FREE|EST
-------------------------------
XENA |2 |0 |0
XENA_IDX1 |1 |0 |0
XENA_IDX2 |1 |0 |0
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
0 rows inserted/updated/deleted
ij> select
cast (conglomeratename as char(10)) as name,
cast (numallocatedpages as char(4)) as aloc,
cast (numfreepages as char(4)) as free,
cast (estimspacesaving as char(10)) as est
from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
NAME |ALOC|FREE|EST
-------------------------------
XENA |2 |0 |0
XENA_IDX1 |1 |0 |0
XENA_IDX2 |1 |0 |0
ij> create table xena3(a int);
0 rows inserted/updated/deleted
ij> delete from xena where b = 3;
1 row inserted/updated/deleted
ij> select
cast (conglomeratename as char(10)) as name,
cast (numallocatedpages as char(4)) as aloc,
cast (numfreepages as char(4)) as free,
cast (estimspacesaving as char(10)) as est
from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
NAME |ALOC|FREE|EST
-------------------------------
XENA |2 |0 |0
XENA_IDX1 |1 |0 |0
XENA_IDX2 |1 |0 |0
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
0 rows inserted/updated/deleted
ij> select
cast (conglomeratename as char(10)) as name,
cast (numallocatedpages as char(4)) as aloc,
cast (numfreepages as char(4)) as free,
cast (estimspacesaving as char(10)) as est
from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
NAME |ALOC|FREE|EST
-------------------------------
XENA |2 |0 |0
XENA_IDX1 |1 |0 |0
XENA_IDX2 |1 |0 |0
ij> create table xena4(a int);
0 rows inserted/updated/deleted
ij> delete from xena where b = 4;
1 row inserted/updated/deleted
ij> select
cast (conglomeratename as char(10)) as name,
cast (numallocatedpages as char(4)) as aloc,
cast (numfreepages as char(4)) as free,
cast (estimspacesaving as char(10)) as est
from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
NAME |ALOC|FREE|EST
-------------------------------
XENA |2 |0 |0
XENA_IDX1 |1 |0 |0
XENA_IDX2 |1 |0 |0
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
0 rows inserted/updated/deleted
ij> select
cast (conglomeratename as char(10)) as name,
cast (numallocatedpages as char(4)) as aloc,
cast (numfreepages as char(4)) as free,
cast (estimspacesaving as char(10)) as est
from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
NAME |ALOC|FREE|EST
-------------------------------
XENA |2 |0 |0
XENA_IDX1 |1 |0 |0
XENA_IDX2 |1 |0 |0
ij> create table xena5(a int);
0 rows inserted/updated/deleted
ij> rollback;
ij> -- should all fail
drop table xena2;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'XENA2' because it does not exist.
ij> drop table xena3;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'XENA3' because it does not exist.
ij> select a, b from xena;
A |B
-----------------------
1 |1
1 |-1
2 |2
2 |-2
3 |3
4 |-4
4 |4
ij> -- read every row and value in the table, including overflow pages.
insert into xena values (select a + 4, b - 4, c, d from xena);
ERROR 42X80: VALUES clause must contain at least one element. Empty elements are not allowed.
ij> insert into xena values (select (a + 4, b - 4, c, d from xena);
ERROR 42X01: Syntax error: Encountered "," at line 1, column 39.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> select
cast (conglomeratename as char(10)) as name,
cast (numallocatedpages as char(4)) as aloc,
cast (numfreepages as char(4)) as free,
cast (estimspacesaving as char(10)) as est
from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
NAME |ALOC|FREE|EST
-------------------------------
XENA |1 |0 |0
XENA_IDX1 |1 |0 |0
XENA_IDX2 |1 |0 |0
ij> -- delete all but 1 row (the sidekick)
delete from xena where a <> 4 or b <> -4;
6 rows inserted/updated/deleted
ij> select
cast (conglomeratename as char(10)) as name,
cast (numallocatedpages as char(4)) as aloc,
cast (numfreepages as char(4)) as free,
cast (estimspacesaving as char(10)) as est
from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
NAME |ALOC|FREE|EST
-------------------------------
XENA |1 |0 |0
XENA_IDX1 |1 |0 |0
XENA_IDX2 |1 |0 |0
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
0 rows inserted/updated/deleted
ij> select
cast (conglomeratename as char(10)) as name,
cast (numallocatedpages as char(4)) as aloc,
cast (numfreepages as char(4)) as free,
cast (estimspacesaving as char(10)) as est
from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
NAME |ALOC|FREE|EST
-------------------------------
XENA |2 |0 |0
XENA_IDX1 |1 |0 |0
XENA_IDX2 |1 |0 |0
ij> rollback;
ij> select a, b from xena;
A |B
-----------------------
1 |1
1 |-1
2 |2
2 |-2
3 |3
4 |-4
4 |4
ij> drop table xena;
0 rows inserted/updated/deleted
ij> -- bug 2940
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');
0 rows inserted/updated/deleted
ij> create table xena (a int, b int, c varchar(1000), d varchar(8000));
0 rows inserted/updated/deleted
ij> insert into xena values (1, 1, 'argo', 'horse');
1 row inserted/updated/deleted
ij> insert into xena values (2, 2, 'beta', 'mule');
1 row inserted/updated/deleted
ij> insert into xena values (3, 3, 'comma', 'horse');
1 row inserted/updated/deleted
ij> insert into xena values (4, 4, 'delta', 'goat');
1 row inserted/updated/deleted
ij> insert into xena values (1, 1, 'x_argo', 'x_horse');
1 row inserted/updated/deleted
ij> insert into xena values (2, 2, 'x_beta', 'x_mule');
1 row inserted/updated/deleted
ij> insert into xena values (3, 3, 'x_comma', 'x_horse');
1 row inserted/updated/deleted
ij> insert into xena values (4, 4, 'x_delta', 'x_goat');
1 row inserted/updated/deleted
ij> autocommit off;
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
0 rows inserted/updated/deleted
ij> commit;
ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4000');
0 rows inserted/updated/deleted
ij> create unique index xena1 on xena (a, c);
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','20000');
0 rows inserted/updated/deleted
ij> create unique index xena2 on xena (a, d);
0 rows inserted/updated/deleted
ij> create unique index xena3 on xena (c, d);
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
0 rows inserted/updated/deleted
ij> select * from xena;
A |B |C |D
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 |1 |argo |horse
2 |2 |beta |mule
3 |3 |comma |horse
4 |4 |delta |goat
1 |1 |x_argo |x_horse
2 |2 |x_beta |x_mule
3 |3 |x_comma |x_horse
4 |4 |x_delta |x_goat
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
0 rows inserted/updated/deleted
ij> select * from xena;
A |B |C |D
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 |1 |argo |horse
2 |2 |beta |mule
3 |3 |comma |horse
4 |4 |delta |goat
1 |1 |x_argo |x_horse
2 |2 |x_beta |x_mule
3 |3 |x_comma |x_horse
4 |4 |x_delta |x_goat
ij> rollback;
ij> select
cast (conglomeratename as char(10)) as name,
cast (numallocatedpages as char(4)) as aloc,
cast (numfreepages as char(4)) as free,
cast (estimspacesaving as char(10)) as est
from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
NAME |ALOC|FREE|EST
-------------------------------
XENA |2 |0 |0
ij> select schemaname, tablename,
SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename)
from sys.systables a, sys.sysschemas b where a.schemaid = b.schemaid
order by schemaname, tablename;
SCHEMANAME |TABLENAME |3
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APP |INDEXES |1
APP |NEWCONGLOM |1
APP |NOINDEXES |1
APP |OLDCONGLOM |1
APP |V_NOINDEXES |1
APP |XENA |1
SYS |SYSALIASES |1
SYS |SYSCHECKS |1
SYS |SYSCOLPERMS |1
SYS |SYSCOLUMNS |1
SYS |SYSCONGLOMERATES |1
SYS |SYSCONSTRAINTS |1
SYS |SYSDEPENDS |1
SYS |SYSFILES |1
SYS |SYSFOREIGNKEYS |1
SYS |SYSKEYS |1
SYS |SYSPERMS |1
SYS |SYSROLES |1
SYS |SYSROUTINEPERMS |1
SYS |SYSSCHEMAS |1
SYS |SYSSEQUENCES |1
SYS |SYSSTATEMENTS |1
SYS |SYSSTATISTICS |1
SYS |SYSTABLEPERMS |1
SYS |SYSTABLES |1
SYS |SYSTRIGGERS |1
SYS |SYSUSERS |1
SYS |SYSVIEWS |1
SYSIBM |SYSDUMMY1 |1
ij> select a, b from xena;
A |B
-----------------------
1 |1
2 |2
3 |3
4 |4
1 |1
2 |2
3 |3
4 |4
ij> -- clean up
drop function padstring;
0 rows inserted/updated/deleted
ij> drop view v_noindexes;
0 rows inserted/updated/deleted
ij> drop table noindexes;
0 rows inserted/updated/deleted
ij> drop table indexes;
0 rows inserted/updated/deleted
ij> drop table oldconglom;
0 rows inserted/updated/deleted
ij> drop table newconglom;
0 rows inserted/updated/deleted
ij> --test case for bug (DERBY-437)
--test compress table with reserved words as table Name/schema Name
create schema "Group";
0 rows inserted/updated/deleted
ij> create table "Group"."Order"("select" int, "delete" int, itemName char(20)) ;
0 rows inserted/updated/deleted
ij> insert into "Group"."Order" values(1, 2, 'memory') ;
1 row inserted/updated/deleted
ij> insert into "Group"."Order" values(3, 4, 'disk') ;
1 row inserted/updated/deleted
ij> insert into "Group"."Order" values(5, 6, 'mouse') ;
1 row inserted/updated/deleted
ij> --following compress call should fail because schema name is not matching the way it is defined using delimited quotes.
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('GROUP', 'Order' , 0) ;
ERROR 38000: The exception 'java.sql.SQLException: Schema 'GROUP' does not exist' was thrown while evaluating an expression.
ERROR 42Y07: Schema 'GROUP' does not exist
ij> --following compress call should fail because table name is not matching the way it is defined in the quotes.
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('Group', 'ORDER' , 0) ;
ERROR 38000: The exception 'java.sql.SQLException: 'ALTER TABLE' cannot be performed on 'Group.ORDER' because it does not exist.' was thrown while evaluating an expression.
ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'Group.ORDER' because it does not exist.
ij> --following compress should pass.
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('Group', 'Order' , 0) ;
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('Group', 'Order' , 1) ;
0 rows inserted/updated/deleted
ij> drop table "Group"."Order";
0 rows inserted/updated/deleted
ij> drop schema "Group" RESTRICT;
0 rows inserted/updated/deleted
ij> ---test undelimited names( All unquoted SQL identfiers should be passed in upper case).
create schema inventory;
0 rows inserted/updated/deleted
ij> create table inventory.orderTable(id int, amount int, itemName char(20)) ;
0 rows inserted/updated/deleted
ij> insert into inventory.orderTable values(101, 5, 'pizza') ;
1 row inserted/updated/deleted
ij> insert into inventory.orderTable values(102, 6, 'coke') ;
1 row inserted/updated/deleted
ij> insert into inventory.orderTable values(103, 7, 'break sticks') ;
1 row inserted/updated/deleted
ij> insert into inventory.orderTable values(104, 8, 'buffolo wings') ;
1 row inserted/updated/deleted
ij> --following compress should fail because schema name is not in upper case.
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('inventory', 'ORDERTABLE' , 0) ;
ERROR 38000: The exception 'java.sql.SQLException: Schema 'inventory' does not exist' was thrown while evaluating an expression.
ERROR 42Y07: Schema 'inventory' does not exist
ij> --following compress should fail because table name is not in upper case.
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('INVENTORY', 'ordertable', 0) ;
ERROR 38000: The exception 'java.sql.SQLException: 'ALTER TABLE' cannot be performed on 'INVENTORY.ordertable' because it does not exist.' was thrown while evaluating an expression.
ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'INVENTORY.ordertable' because it does not exist.
ij> --following compress should pass.
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('INVENTORY', 'ORDERTABLE' , 1) ;
0 rows inserted/updated/deleted
ij> drop table inventory.orderTable;
0 rows inserted/updated/deleted
ij> drop schema inventory RESTRICT;
0 rows inserted/updated/deleted
ij> --end derby-437 related test cases.
-- test case for derby-1854
-- perform compress on a table that has same column
-- as a primary key and a foreign key.
create table users (
user_id int not null generated by default as identity,
user_login varchar(255) not null,
primary key (user_id));
0 rows inserted/updated/deleted
ij> create table admins (
user_id int not null,
primary key (user_id),
constraint admin_uid_fk foreign key (user_id) references users (user_id));
0 rows inserted/updated/deleted
ij> insert into users (user_login) values('test1');
1 row inserted/updated/deleted
ij> insert into admins values (values identity_val_local());
1 row inserted/updated/deleted
ij> call syscs_util.syscs_compress_table('APP', 'ADMINS', 0);
0 rows inserted/updated/deleted
ij> -- do consistency check on the tables.
values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'USERS');
1
-----------
1
ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'ADMINS');
1
-----------
1
ij> select * from admins;
USER_ID
-----------
1
ij> select * from users;
USER_ID |USER_LOGIN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 |test1
ij> insert into users (user_login) values('test2');
1 row inserted/updated/deleted
ij> insert into admins values (values identity_val_local());
1 row inserted/updated/deleted
ij> drop table admins;
0 rows inserted/updated/deleted
ij> drop table users;
0 rows inserted/updated/deleted
ij> -- end derby-1854 test case.
-- test case for derby-737
-- perform compress on a table that has some indexes with no statistics
create table derby737table1 (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> select * from sys.sysstatistics;
STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij> -- create index on the table when the table is empty. No statistics will be
-- generated for that index
create index t1i1 on derby737table1(c1);
0 rows inserted/updated/deleted
ij> select * from sys.sysstatistics;
STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij> -- the insert above will not add a row into sys.sysstatistics for index t1i1
insert into derby737table1 values(1,1);
1 row inserted/updated/deleted
ij> select * from sys.sysstatistics;
STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij> -- now compress the table and as part of the compress, Derby should generate
-- statistics for all the indexes provided the table is not empty
call syscs_util.syscs_compress_table('APP','DERBY737TABLE1',1);
0 rows inserted/updated/deleted
ij> -- Will find statistics for index t1i1 on derby737table1 because compress
-- table created it.
select * from sys.sysstatistics;
STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx|I|true |1 |numunique= 1 n&
ij> drop table derby737table1;
0 rows inserted/updated/deleted
ij> -- Next Test : Make sure that drop index will drop the existing statistics
create table derby737table2 (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> insert into derby737table2 values(1,1),(2,2);
2 rows inserted/updated/deleted
ij> select * from sys.sysstatistics;
STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij> -- since there is data in derby737table2 when index is getting created,
-- statistics will be created for that index
create index t2i1 on derby737table2(c1);
0 rows inserted/updated/deleted
ij> select * from sys.sysstatistics;
STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx|I|true |1 |numunique= 2 n&
ij> -- deleting all the rows in table will not drop the index statistics
delete from derby737table2;
2 rows inserted/updated/deleted
ij> select * from sys.sysstatistics;
STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx|I|true |1 |numunique= 2 n&
ij> -- dropping index will drop the index statistics, if they exist
drop index t2i1;
0 rows inserted/updated/deleted
ij> select * from sys.sysstatistics;
STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij> -- Next Test : Male sure that compress table will drop the existing statistics
-- and will not recreate them if the table is empty
insert into derby737table2 values(1,1),(2,2);
2 rows inserted/updated/deleted
ij> select * from sys.sysstatistics;
STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij> -- since there is data in derby737table2 when index is getting created,
-- statistics will be created for that index
create index t2i1 on derby737table2(c1);
0 rows inserted/updated/deleted
ij> select * from sys.sysstatistics;
STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx|I|true |1 |numunique= 2 n&
ij> -- deleting all the rows in table will not drop the index statistics
delete from derby737table2;
2 rows inserted/updated/deleted
ij> select * from sys.sysstatistics;
STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx|I|true |1 |numunique= 2 n&
ij> -- now compress the table and as part of the compress, Derby should drop
-- statistics for all the indexes and should not recreate them if the
-- user table is empty
call syscs_util.syscs_compress_table('APP','DERBY737TABLE2',1);
0 rows inserted/updated/deleted
ij> select * from sys.sysstatistics;
STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ij> --end derby-737 related test cases.
-- DERBY-2057
-- Use non-zero args other than 1s.
rollback;
ij> autocommit on;
ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');
0 rows inserted/updated/deleted
ij> create table t1 (c1 char(254));
0 rows inserted/updated/deleted
ij> insert into t1 values 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z';
26 rows inserted/updated/deleted
ij> select conglomeratename, numallocatedpages, numfreepages from new org.apache.derby.diag.SpaceTable('T1') tab;
CONGLOMERATENAME |NUMALLOCATEDPAGES |NUMFREEPAGES
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
T1 |3 |0
ij> delete from t1;
26 rows inserted/updated/deleted
ij> -- don't check result from delete as the number of free pages is system
-- performance dependent. It depends on how quickly post commit can run and
-- reclaim the space, the result will not be reproducible across all platforms.
-- select conglomeratename, numallocatedpages, numfreepages from new org.apache.derby.diag.SpaceTable('T1') tab;
call syscs_util.syscs_inplace_compress_table('APP','T1',2,2,2);
0 rows inserted/updated/deleted
ij> select conglomeratename, numallocatedpages, numfreepages from new org.apache.derby.diag.SpaceTable('T1') tab;
CONGLOMERATENAME |NUMALLOCATEDPAGES |NUMFREEPAGES
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
T1 |1 |0
ij> drop table t1;
0 rows inserted/updated/deleted
ij>