SQL/MP Installation and Management Guide

HP NonStop SQL/MP Installation and Management Guide—523353-004
8-1
8
Reorganizing Tables and Maintaining
Data
Sometimes you might need to restructure the data in a table by reloading or
reorganizing the table. You might do this restructuring, for example, when you want to
perform operations such as:
•
Reload the data to increase the data-block free space, reducing block splits during
insertions and updates
•
Partition or repartition a table or an index
•
Split a partition to create additional space by distributing data across volumes
•
Organize data blocks to eliminate empty space and reduce the number of index
levels
•
Increase the available disk space when the data or index block structure becomes
very fragmented
•
Reduce empty but allocated free space that occurs when a large number of
records are deleted from a file
•
Compress a file to improve disk space usage
An essential step in creating a DSS database is to populate the tables with data
derived from operational systems. SQL/MP provides the SQLCI LOAD utility to move
data into a table.
To maintain the data in a DSS database, you must periodically add new data and purge
the oldest data from the database. SQL/MP provides several SQLCI utilities, including
APPEND, COPY, and PURGEDATA, to perform these data-maintenance operations.
Choosing a Reorganization Method
Three methods are available to perform the restructuring function: online
reorganization through the RELOAD command of FUP, offline reorganization through
the LOAD or COPY command of SQLCI, and physical reorganization through partition
move operations.
These restructuring methods have these capabilities and restrictions:
•
Reorganizing a table online with the FUP RELOAD command:
°
The table must be a key-sequenced table.
°
The table is accessible for use by the application at all times because of the
shared-mode reorganization method.