SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—544536-007
10-2
Using FUP RELOAD to Reorganize Tables
If this directory becomes too full, NonStop SQL/MX will not drop any more objects until
more space is made available. To prevent this from happening, periodically check
(once a week, for example) the DDL directory and purge all DDL files for tables you
know will not have to be recovered. The DDL directory contains timestamp information
for each DDL file that identifies the time and date when a table was dropped.
If you do not want NonStop SQL/MX to save DDL when you drop table, you can turn
this feature off by setting the SAVE_DROPPED_TABLE_DDL control query to “OFF.”
For more information, see the SQL/MX Reference Manual.
Using FUP RELOAD to Reorganize Tables
Of all the SQL/MX object types, only tables and indexes contain significant amounts of
data. Views, trigger temporary tables, and stored procedures do not usually need to be
reloaded.
Use FUP RELOAD to reload a table when:
•
You have confirmed that a table is fragmented by using FUP INFO to read the
index_level value.
•
You need to insert slack space into a table file.
SQL/MX tables can be reorganized online. Online reorganization involves:
•
Reorganizing Table Files With FUP RELOAD on page 10-2
•
Determining the Status of a Reorganization With FUP STATUS on page 10-3
•
Suspending a Reorganization Operation on page 10-4
After you use FUP RELOAD to defragment a table, update the physical statistics using
UPDATE STATISTICS to update the metadata information. This allows the optimizer to
generate better query plans.
Reorganizing Table Files With FUP RELOAD
The FUP RELOAD command lets you reorganize a key-sequenced file while the file
remains available for use by the application. The FUP RELOAD operation physically
restructures the file to improve access performance and space usage.
Before performing a FUP RELOAD operation, consider these issues:
•
During the operation, performance can be degraded if the data blocks have
become physically disorganized. Disorganization can occur as a result of INSERT,
UPDATE, or DELETE operations performed over a period of time. You can,
however, control the amount of degradation by using the command’s RATE option.
The higher the rate, the faster the reload occurs, but the more performance
degrades. Conversely, the lower the rate, the slower the reload occurs, but the less
performance degrades. The default value for RATE is 100 percent.