📄 compresstable.out
字号:
1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------No open scans, etc.3 dependencies found ij> rollback;ij> -- verify statements get re-preparedcreate table t(c1 int, c2 int);0 rows inserted/updated/deletedij> insert into t values (1, 2), (3, 4), (5, 6);3 rows inserted/updated/deletedij> 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/deletedij> 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 reclaimedcall SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');0 rows inserted/updated/deletedij> create table t(c1 int, c2 varchar(1500));0 rows inserted/updated/deletedij> 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/deletedij> create table oldinfo (cname varchar(128), nap bigint);0 rows inserted/updated/deletedij> insert into oldinfo select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('T') t;1 row inserted/updated/deletedij> delete from t where c1 in (1, 3, 5, 7);4 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T', 0);0 rows inserted/updated/deletedij> create table newinfo (cname varchar(128), nap bigint);0 rows inserted/updated/deletedij> insert into newinfo select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('T') t;1 row inserted/updated/deletedij> -- verify space reclaimed, this query should return 'compressed!'-- if nothing is returned from this query, then the table was not compressedselect '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 tablecall SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 1);0 rows inserted/updated/deletedij> select * from v_noindexes;C1 |C2 |C3 ---------------------------------------------------ij> -- full tableinsert 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/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 1);0 rows inserted/updated/deletedij> 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/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 1);0 rows inserted/updated/deletedij> 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/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 1);0 rows inserted/updated/deletedij> 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 indexdrop index i_c2;0 rows inserted/updated/deletedij> drop index i_c3;0 rows inserted/updated/deletedij> drop index i_c2c1;0 rows inserted/updated/deletedij> drop index i_c3c1;0 rows inserted/updated/deletedij> -- empty tablecall SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 1);0 rows inserted/updated/deletedij> select * from indexes;C1 |C2 |C3 ---------------------------------------------------ij> -- full tableinsert 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);⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -