📄 compresstable.out
字号:
ij> insert into tab values (7, 7, 'abc'), (8, 8, 'bcd');2 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB', 1);0 rows inserted/updated/deletedij> 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 rowscreate table oldstat(rowCount int);0 rows inserted/updated/deletedij> insert into oldstat select count(*) from tab;1 row inserted/updated/deletedij> commit;ij> --double the size of the tableselect 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/deletedij> insert into tab values (3, 3, 'abc'), (4, 4, 'bcd');2 rows inserted/updated/deletedij> insert into tab values (5, 5, 'abc'), (6, 6, 'bcd');2 rows inserted/updated/deletedij> insert into tab values (7, 7, 'abc'), (8, 8, 'bcd');2 rows inserted/updated/deletedij> 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/deletedij> 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/deletedij> -- verify space reclaimedselect 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 rowscreate table newstat(rowCount int);0 rows inserted/updated/deletedij> insert into newstat select count(*) from tab;1 row inserted/updated/deletedij> --make sure the number of rows are the sameselect o.rowCount, n.rowCount from oldstat o, newstat n where o.rowCount = n.rowCount;ROWCOUNT |ROWCOUNT -----------------------8 |8 ij> --show old space usageselect 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/deletedij> --show new space usageselect 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/deletedij> drop table oldstat;0 rows inserted/updated/deletedij> -- test that many levels of aborts of compress table still workcall SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');0 rows inserted/updated/deletedij> create table xena (a int, b int, c varchar(1000), d varchar(8000));0 rows inserted/updated/deletedij> create index xena_idx1 on xena (a, c);0 rows inserted/updated/deletedij> create unique index xena_idx2 on xena (b, c);0 rows inserted/updated/deletedij> insert into xena values (1, 1, 'argo', 'horse');1 row inserted/updated/deletedij> insert into xena values (1, -1, 'argo', 'horse');1 row inserted/updated/deletedij> insert into xena values (2, 2, 'ares', 'god of war');1 row inserted/updated/deletedij> insert into xena values (2, -2, 'ares', 'god of war');1 row inserted/updated/deletedij> insert into xena values (3, 3, 'joxer', 'the mighty');1 row inserted/updated/deletedij> insert into xena values (4, -4, 'gabrielle', 'side kick');1 row inserted/updated/deletedij> insert into xena values (4, 4, 'gabrielle', 'side kick');1 row inserted/updated/deletedij> 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/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);0 rows inserted/updated/deletedij> 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⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -