Space Requirements for Modify Operations

Modify operations require additional working disk space because a new version of the table must be built before the old table can be removed. In most cases, modify operations require about two or three times more space than the original table size. This is only an approximation; the amount of disk space actually needed can vary.

The free disk space is also required during modify to relocate and modify to reorganize operations.

For information about relocating and changing the location of storage structures, see the Modifying Storage Structures topic in online help for VDBA. For details on the DBA use of these operations, see Techniques for Moving a Table to a New Location.

In SQL, you can accomplish these tasks with the modify to reorganize and modify to relocate statements. For more information, see the SQL Reference Guide.

The maintain_locations privilege is needed to perform the operation on location objects in VDBA or to issue the modify location statement in SQL. The maintain_locations privilege allows users to do the following:

Control the allocation of disk space
Create new locations or allow new locations to be created
Modify or remove existing locations

Factors Affecting Space Requirements for Modify Operations

The following are important factors that affect disk space requirements:

You need at least twice the disk space of the table ("2X"), one copy of the original table and one copy of the new table.
The new table size can be increased if an index is being added. Conversely, space can be freed if an index is no longer necessary. Index space can vary widely depending on the size of the key.
If you are modifying to a sorted structure (ISAM, B-tree) or to hash, an additional copy of the original table is needed, thus requiring three times the disk space of the table ("3X").
If you are modifying a compressed table, calculate disk space based on the uncompressed size. In the worst case, this is the row size times the number of rows.
Usually going from a compressed structure to an uncompressed structure increases the table size, and going the other way decreases its size. The amount of change cannot be predicted and is dependent on the data in the table. If many NULL values are present and if many string fields have trailing blanks, the use or omission of compression is very noticeable.
Fill factor, minpages, leaffill, and nonleaffill also play a role in the resulting table size. For details, see Options to the Modify Procedure.

Summary of Space Requirements for Modify Operations

The following table provides a summary for estimating disk space requirements.

In the table, "O+N" (Original+New tables) corresponds roughly to twice the table size (2X) and "O+N+S" to three times the table size (3X). The space required can be affected by whether an index is added ("I" in the table) or existing index space freed ("U" in the table).

Original Table Structure	Modified to
	Heap	Hash	ISAM	B-tree
Heap	O+N	O+N+S	O+N+S+I	O+N+S+I
Hash	O+N	O+N+S	O+N+S+I	O+N+S+I
ISAM	O+N-U	O+N+S-U	O+N+S	O+N+S
B-tree	O+N-U	O+N+S-U	O+N+S	O+N+S

Legend:

O = Original table size
N = New table size
S = A sort is required
I = Index is being added
U = Space freed because an index is no longer necessary

Note: Remember that numerous factors contribute to the actual disk space used in a particular modify operation. Additional factors include compression and the various fill values.