📄 compresstable.out
字号:
ij> -- tests for system procedure SYSCS_COMPRESS_TABLE-- that reclaims disk space to the OSrun resource 'createTestProcedures.subsql';ij> 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/deletedij> 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/deletedij> 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/deletedij> -- create tablescreate table noindexes(c1 int, c2 char(30), c3 decimal(5,2));0 rows inserted/updated/deletedij> create table indexes(c1 int, c2 char(30), c3 decimal(5,2));0 rows inserted/updated/deletedij> create index i_c1 on indexes(c1);0 rows inserted/updated/deletedij> create index i_c2 on indexes(c2);0 rows inserted/updated/deletedij> create index i_c3 on indexes(c3);0 rows inserted/updated/deletedij> create index i_c3c1 on indexes(c3, c1);0 rows inserted/updated/deletedij> create index i_c2c1 on indexes(c2, c1);0 rows inserted/updated/deletedij> create table oldconglom(o_cnum bigint, o_cname long varchar);0 rows inserted/updated/deletedij> create table newconglom(n_cnum bigint, n_cname long varchar);0 rows inserted/updated/deletedij> create view v_noindexes as select * from noindexes;0 rows inserted/updated/deletedij> autocommit off;ij> -- test with heap only-- test with empty tableinsert into oldconglomselect conglomeratenumber, conglomeratename from sys.systables t, sys.sysconglomerates cwhere t.tablename = 'NOINDEXES' and t.tableid = c.tableid;1 row inserted/updated/deletedij> select count(*) from oldconglom;1 -----------1 ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 0);0 rows inserted/updated/deletedij> insert into newconglomselect conglomeratenumber, conglomeratename from sys.systables t, sys.sysconglomerates cwhere t.tablename = 'NOINDEXES' and t.tableid = c.tableid;1 row 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 -----------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 fetchinsert 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', 0);0 rows inserted/updated/deletedij> 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/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 0);0 rows inserted/updated/deletedij> 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/deletedij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'NOINDEXES', 0);0 rows inserted/updated/deletedij> 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 tableinsert into oldconglomselect conglomeratenumber, conglomeratename from sys.systables t, sys.sysconglomerates cwhere t.tablename = 'INDEXES' and t.tableid = c.tableid;6 rows inserted/updated/deletedij> select count(*) from oldconglom;1 -----------6 ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'INDEXES', 0);0 rows inserted/updated/deletedij> insert into newconglomselect conglomeratenumber, conglomeratename from sys.systables t, sys.sysconglomerates cwhere t.tablename = 'INDEXES' and t.tableid = c.tableid;⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -