📄 compresstable.out
字号:
6 rows inserted/updated/deletedij> 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 fetchinsert 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/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 0);0 rows inserted/updated/deletedij> 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/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 0);0 rows inserted/updated/deletedij> 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/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 0);0 rows inserted/updated/deletedij> 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 keyscreate 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/deletedij> create table f (x int, t int, y int, constraint fk foreign key (x,y) references p);0 rows inserted/updated/deletedij> insert into p values ('1', 1, '1', 1);1 row inserted/updated/deletedij> insert into f values (1, 1, 1), (1, 1, null);2 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'P', 0);0 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'F', 0);0 rows inserted/updated/deletedij> insert into f values (1, 1, 1);1 row inserted/updated/deletedij> 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/deletedij> insert into f values (2, 2, 2);1 row inserted/updated/deletedij> -- do consistency check on scans, etc.values ConsistencyChecker();1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------No open scans, etc.4 dependencies found ij> rollback;ij> -- self referencing tablecreate table pf (x int not null constraint p primary key, y int constraint f references pf);0 rows inserted/updated/deletedij> insert into pf values (1,1), (2, 2);2 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'PF', 0);0 rows inserted/updated/deletedij> insert into pf values (3,1), (4, 2);2 rows inserted/updated/deletedij> 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 columncall SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');0 rows inserted/updated/deletedij> create table t (i int, s varchar(1500));0 rows inserted/updated/deletedij> create index t_s on t(s);0 rows inserted/updated/deletedij> create index t_si on t(s, i);0 rows inserted/updated/deletedij> insert into t values (1, '1'), (2, '2');2 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T', 0);0 rows inserted/updated/deletedij> select * from t;I |S --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 |1 2 |2 ij> -- do consistency check on scans, etc.values ConsistencyChecker();⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -