compresstable.out

derby database source code.good for you.

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