|
|
|
To rebuild the existing storage structure for a table or partition, use the MODIFY...TO RECONSTRUCT option.
The reconstruct action allows the table or partitions to be rebuilt, maintaining the existing storage structure, key columns, and storage attributes. Any overrides specified in the modify statement with-clause are applied.
The reconstruct variant provides a simple means for partitioning or unpartitioning a table without affecting its other attributes. Partitioning or unpartitioning a table requires rewriting its storage structure, which is why partitioning is limited to restructuring variants of the modify statement.
The heapsort structure is not really a storage structure, in the sense that the sort criteria are not remembered in any system catalog. Therefore reconstruction of a table originally modified to heapsort simply remodifies the table to heap with no additional sorting.
When operating on specific logical partitions instead of an entire table, the reconstruct modify does not permit any override with-attributes except for the location option.
The partition name clause allows the modify statement to operate on specific named logical partitions. Partition names must be listed from outer dimension to inner dimension.
Note: Modify...to reconstruct does not work on secondary indexes.
|
|
|
To shrink a btree index, use the MODIFY...TO MERGE option. When data is added to a btree table, the index automatically expands. However, a btree index does not shrink when rows are deleted from the btree table.
Modify...to merge affects only the index, and therefore usually runs a good deal faster than the other modify variants. Modify...to merge does not require any temporary disk space to execute.
|
|
|
To move the data without changing the number of locations or storage structure, specify RELOCATE.
For example, to relocate the employee table to three different areas:
modify employee to relocate
with oldlocation = (area1, area2, area3),
newlocation = (area4, area5, area6);
The data in area1is moved to area4, the data in area2 is moved to area5, and the data on area3 is moved to area6. The number of areas listed in the oldlocation and newlocation options must be equal. The data in each area listed in the oldlocation list is moved "as is" to the corresponding area in the newlocation list. The oldlocation and newlocation options can only be used when relocate is specified.
To change some but not all locations, specify only the locations to be changed. For example, you can move only the data in area1 of the employee table:
modify employee to relocate
with oldlocation = (area1),
newlocation = (area4);
Areas 2 and 3 are not changed.
The DBMS Server is very efficient at spreading a table or index across multiple locations. For example, if a table is to be spread over three locations:
create table large (wide varchar(2000),
with location = (area1, area2, area3);
rows are added to each location in turn, in 16 page (approximately 32K for the default 2K page size) chunks. If it is not possible to allocate 16 full pages on an area when it is that area's turn to be filled, the table is out of space, even if there is plenty of room in the table's other areas.
|
|
|
To move the data and change the number of locations without changing storage structure, specify REORGANIZE. For example, to spread an employee table over three locations:
modify employee to reorganize
with location = (area1, area2, area3);
When specifying reorganize, the only valid with clause option is location.