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

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—523723-004
10-2
Choosing a Reorganization Method
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 the DDL when you drop a 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.
Choosing a Reorganization Method
You can perform the restructuring function in NonStop SQL/MX by:
•
Using FUP RELOAD to Reorganize Tables on page 10-2
•
Using MODIFY to Manage Table and Index Partitions on page 10-9
•
Using import to Load and Append Input Data Into SQL/MX Tables on page 10-22
•
Using DUP to Copy Tables Into Tables on page 10-34
•
Using PURGEDATA to Delete Data From Tables on page 10-35
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: