Derby database to help thin the physical file.

2011-09-23  来源:本站原创  分类:Database  人气:53 

Recently, the project quickly ending the need to clean up the database (Derby): clean up unused data, reducing the database size.

Finished cleaning up and found that the physical database file size is not reduced. Derby did not know what kind of mechanism, the physical file weight? Less than the entry-level English level I do not want to see the official documents in English, direct google about and find this link: http://mxh85154.blog.hexun.com/33808923_d.html , provided the original stored procedure to thin Derby.

Described in the article was not very detailed look Derby had their own documents, as seen from the document text:
SYSCS_UTIL.SYSCS_COMPRESS_TABLE
Use the SYSCS_UTIL.SYSCS_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, or indexes are updated. 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_COMPRESS_TABLE allows you to return unused space to the operating system.
Syntax
SYSCS_UTIL.SYSCS_COMPRESS_TABLE (IN SCHEMANAME VARCHAR (128),
IN TABLENAME VARCHAR (128), IN SEQUENTIAL SMALLINT)
SCHEMANAME
An input argument of type VARCHAR (128) that specifies the schema of the table. Passing a null will result in an error.
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.
SEQUENTIAL
A non-zero input argument of type SMALLINT will force the operation to run in sequential mode, while an argument of 0 will force the operation not to run in sequential mode. Passing a null will result in an error.
SQL example
To compress a table called CUSTOMER in a schema called US, using the SEQUENTIAL option:
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE ('US', 'CUSTOMER', 1)

As can be seen from the document, called this process, need to pass in three parameters: the first is a database of SCHEME, the second for the table name, and the third is a short non-zero integer, and the three parameters can not be empty. According to documents introduced to try, really worked, the file size reduced by the 850M to 40M. But too much trouble, you need a table for each stored procedure execution time. Later, I thought I could use stored procedures, one to clean up all the tables: in the storage process, through the statement "SELECT * FROM SYS.SYSTABLES T WHERE T. TABLETYPE = 'T';" check out all of the users table, and then clean up .

Finally, there is a doubt, stored procedure, the interpretation of the third parameter is not very clear: the different incoming short integers, the results will be any different?

相关文章
  • Derby database to help thin the physical file. 2011-09-23

    Recently, the project quickly ending the need to clean up the database (Derby): clean up unused data, reducing the database size. Finished cleaning up and found that the physical database file size is not reduced. Derby did not know what kind of mech

  • About-Derby Derby database using the entry 2010-04-10

    Derby database is an open source under Apache DB project, compared with the other DB, which has the following advantages: 1. Entirely in Java Implementation 2. Supports JDBC and SQL standards 3. All the documents only about 2M 4. To provide embedded

  • the composition of the physical file mysql 2010-06-21

    I put my mysql data file assigned to the following directory: [Root @ txwhdb data_1] # pwd / Data_1 The directory structure is as follows: Which is a database directory shengqian mysql and other documents are 1, mysql error log file: txwhdb.err mysql

  • sql server 2000 change database name. to change the logical file name! 2010-09-24

    sql server 2000 change database name, change the logical file name 2010-09-24 11:00:00. 1. To change the database physical file name in the Enterprise Manager can not directly change to run in Query Analyzer (database name): alter database name of th

  • Derby database via JDBC operations (two) 2011-08-12

    Derby Version: db-derby-10.8.1.2 Catalog Analysis: 1) bin directory, the script contains a number of tools and equipment for the environment script; 2) demo directory contains some example programs; 3) docs directory contains the Derby documentation;

  • JDBC access the embedded Derby database 2010-12-27

    Apache Derby: http://db.apache.org/derby/index.html At a recent amateur derby project with a task of creating a database to store and complete records. apache derby is an open source database products, is rich in features. It supports client / server

  • Find a hibernate issue for the derby database bug, and solutions 2011-01-01

    / / Recent apache Software Foundation, released the derby10.7, due to the 10.7 version to support the derby data types boolean, today found a derby database hibernate for a bug, deliberately posted it to remind others. / / 2011-01-01 discovered hiber

  • Oracle links database four ways (thin and oci) 2011-03-22

    Oracle provides four different types of JDBC drivers, for use in different deployment scenarios. The 10.1.0 drivers can access Oracle 8.1.7 and higher. While all Oracle JDBC drivers are similar, some features apply only to JDBC OCI drivers and some a

  • Try to expand the physical file, MODIFY FILE encountered operating system error 2010-08-04

    Solutions to the FAT32 file system is converted to NTFS the NTFS FAT32 transfer method: Do not need to format, run: enter the command convert c: / fs: ntfs Where, c is the drive letter as long as it can turn the FAT32 file system, NTFS has re!

  • Read excerpt 3 --- Oracle sequence of use, restricted session, instance and database difference 2010-10-07

    1 for data items to be increasing problems, the correct solution for each database using the most appropriate code. In Oracle, the code should be as follows (Assuming the table T needs generated by the primary key): create table t (pk number primary

  • A database upgrade process mysql5.0.78 to 5.1.57 2011-06-13

    1 Stop the database, backup data (backup the physical file) (2) the new version of the mysql data directory pointing to the old directory (datadir) 3. Start mysqld with skip grant tables when the system checks to avoid arguments, # Example / Usr / lo

  • Sql Server 2000 database backup file to restore 2010-03-17

    SQL Server 2000 database backup file to restore 1, open Sql server 2000 database, right on the label in the database, select - "All Tasks -" Restore Database 2, pop-up dialog box to restore database (1) in the "General" tab "to re

  • sql server 2000 change database name. change the logical file name! 2010-09-24

    sql server 2000 change database name, change the logical file name 2010-09-24 11:00:00. One. Change the database physical file name in Enterprise Manager can not directly change to run in Query Analyzer (database name): the original name of the datab

  • Mysql database server configuration file / etc / my.cnf configuration details 2010-10-24

    The following is a Mysql database server configuration file / etc / my.cnf configuration details. Applications is the InnoDB engine, 4-core CPU, 32 Bit SUSE. [Client] port = 3306 socket = / tmp / mysql.sock # The MySQL server [Mysqld] bind-address =

  • . Mdf file into SqlServer database to [transfer] 2010-12-12

    Tasks: to re-import can be used PUBS sp_attach_db, very simple test PUBS passed. Process: 1. Stopped MSSQLSERVER, MSSQL installation directory to E: \ Program Files \ Microsoft SQL Server \ MSSQL \ Data \ pubs.mdf and E: \ Program Files \ Microsoft S

  • Storage system: the file system and database system of difference 2010-12-25

    This time around the company made a temporary feature, briefly describe the temporary storage of data in the page there is a temporary file, the second came in the page buffer to read the data. Then think of such a problem, the data stored in the dat

  • Physical distribution of database design 2011-07-24

    Turn: the physical distribution of database design Overview We Regardless of the database, regardless of how to design a database, I think will follow the principle: data security and performance efficiency of these two main aspects, but on these two

  • dataguard physical standby database to switch error log 2010-08-30

    Log primary database, query v $ database view switchover_status column. E: \ ora10g> set oracle_sid = geo1pri E: \ ora10g> sqlplus "/ as sysdba" SQL * Plus: Release 10.2.0.3.0 - Production on Thursday December 13 09:41:29 2007 Copyright (c

  • SQL SERVER 2000 database backup file *. bak reduction 2010-10-13

    Recently, I purchased a virtual space, the database is SQL SERVER, database backup of the database support for the BAK file, because I learn a good move, accidentally deleted the all the database, but fortunately there is a remote server backup, so h

  • Storage system: file system and database system of difference 2010-12-25

    This time around the company made a temporary function, briefly describe the temporary storage, the page data is temporarily stored in a file, the second came in to read this page on the temporary data. Then think of a question, the data stored in th