[ http://issues.apache.org/jira/browse/DERBY-367?page=comments#action_12314634 ]
Suresh Thalamati commented on DERBY-367: ---------------------------------------- It might be good idea to mention about this new procedure in Server/Admin Guide: Reclaiming unused space: Chapter . > include documentation for SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE() in the > documentation > ---------------------------------------------------------------------------------------- > > Key: DERBY-367 > URL: http://issues.apache.org/jira/browse/DERBY-367 > Project: Derby > Type: Improvement > Components: Documentation > Versions: 10.1.1.0 > Reporter: Mike Matrigali > Assignee: Jeff Levitt > Priority: Minor > Fix For: 10.1.1.0 > Attachments: derby367modified.zip > > Include documentation for SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE in the > reference manual. It should be in the same section as the current > documentation for SYSCS_UTIL.SYSCS_COMPRESS_TABLE() > Here is a badly formatted version of what should go there: > <p> > Use the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE system procedure to reclaim > unused, allocated space in a table and its indexes. Typically, unused > allocated > space exists when a large amount of data is deleted from a table, and there > have not been subsequent inserts to use the space freed by the deletes. > By default, Derby does not return unused space to the operating system. For > example, once a page has been allocated to a table or index, it is not > automatically returned to the operating system until the table or index is > destroyed. SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE allows you to return > unused > space to the operating system. > <p> > This system procedure can be used to force 3 levels of in place compression > of a SQL table: PURGE_ROWS, DEFRAGMENT_ROWS, TRUNCATE_END. Unlike > SYSCS_UTIL.SYSCS_COMPRESS_TABLE() all work is done in place in the existing > table/index. > <p> > Syntax: > SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE( > IN SCHEMANAME VARCHAR(128), > IN TABLENAME VARCHAR(128), > IN PURGE_ROWS SMALLINT, > IN DEFRAGMENT_ROWS SMALLINT, > IN TRUNCATE_END SMALLINT) > <p> > SCHEMANAME: > An input argument of type VARCHAR(128) that specifies the schema of the > table. Passing a null will result in an error. > <p> > TABLENAME: > An input argument of type VARCHAR(128) that specifies the table name of the > table. The string must exactly match the case of the table name, and the > argument of "Fred" will be passed to SQL as the delimited identifier 'Fred'. > Passing a null will result in an error. > <p> > PURGE_ROWS: > If PURGE_ROWS is set to non-zero then a single pass is made through the table > which will purge committed deleted rows from the table. This space is then > available for future inserted rows, but remains allocated to the table. > As this option scans every page of the table, it's performance is linearly > related to the size of the table. > <p> > DEFRAGMENT_ROWS: > If DEFRAGMENT_ROWS is set to non-zero then a single defragment pass is made > which will move existing rows from the end of the table towards the front > of the table. The goal of the defragment run is to empty a set of pages > at the end of the table which can then be returned to the OS by the > TRUNCATE_END option. It is recommended to only run DEFRAGMENT_ROWS, if also > specifying the TRUNCATE_END option. This option scans the whole table and > needs to update index entries for every base table row move, and thus > execution > time is linearly related to the size of the table. > <p> > TRUNCATE_END: > If TRUNCATE_END is set to non-zero then all contiguous pages at the end of > the table will be returned to the OS. Running the PURGE_ROWS and/or > DEFRAGMENT_ROWS passes options may increase the number of pages affected. > This option itself does no scans of the table, so performs on the order of a > few system calls. > <p> > SQL example: > To compress a table called CUSTOMER in a schema called US, using all > available compress options: > call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('US', 'CUSTOMER', 1, 1, 1); > To quickly just return the empty free space at the end of the same table, > this option will run much quicker than running all phases but will likely > return much less space: > call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('US', 'CUSTOMER', 0, 0, 1); > Java example: > To compress a table called CUSTOMER in a schema called US, using all > available compress options: > CallableStatement cs = conn.prepareCall > ("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?, ?, ?)"); > cs.setString(1, "US"); > cs.setString(2, "CUSTOMER"); > cs.setShort(3, (short) 1); > cs.setShort(4, (short) 1); > cs.setShort(5, (short) 1); > cs.execute(); > To quickly just return the empty free space at the end of the same table, > this option will run much quicker than running all phases but will likely > return much less space: > CallableStatement cs = conn.prepareCall > ("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?, ?, ?)"); > cs.setString(1, "US"); > cs.setString(2, "CUSTOMER"); > cs.setShort(3, (short) 0); > cs.setShort(4, (short) 0); > cs.setShort(5, (short) 1); > cs.execute(); > <p> > It is recommended that the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure is > issued in auto-commit mode. > Note: This procedure acquires an exclusive table lock on the table being > compressed. All statement plans dependent on the table or its indexes are > invalidated. For information on identifying unused space, see the Derby > Server and Administration Guide. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
