Database Administrator Guide > Database Administrator Guide > Managing Tables and Views > Table Management > Techniques for Moving a Table to a New Location
Was this helpful?
Techniques for Moving a Table to a New Location
As a database grows, it can become necessary to move some of its tables to an alternate location. If a table has grown so large that you can no longer work with it at the current location, or the table needs to be distributed across multiple disks to improve performance, modify the table to use an alternate location.
Note:  Before modifying the table, make sure you have met the requirements, as described in Requirements for Using an Alternate Location for a Table.
You can modify a table's location using one of the following techniques:
• Relocate a table
• Reorganize a table
In VBDA, use the Modify Table Structure dialog. For a detailed procedure, see online help. For other uses of this dialog, see the chapter “Maintaining Storage Structures.”
Relocate a Table
You can move the data files for a table from one location to another by using the MODIFY TO RELOCATE statement.
In VDBA, this is accomplished using the Modify Table Structure dialog. When the Relocate radio button is enabled, click the Locations button, which opens the Relocate Table dialog. For information on using this dialog, see online help.
Using this operation, it is possible to change one or more of the locations currently used by the table, without changing the number of locations used. For example:
• If a table is using a single location, choose a new location and the data files for the table are moved to the new location.
• If a table is using multiple locations, selectively specify which ones you want to change.
Reorganize a Table
You can increase or decrease the number of locations used by a table for its data files.
To do this, use the SQL statement MODIFY...TO REORGANIZE. In VDBA, use the Modify Table Structure dialog. Use the Locations button, which is available when the Change Locations radio button is enabled, which opens the Change Locations dialog. For specific information on using this dialog, see online help.
This operation requires more overhead than simply relocating a table because it performs a table reorganization in addition to moving files. Using this operation, you can:
• Expand a table that is using a single location to use multiple locations, including the option of no longer using the original location.
• Shrink a table that is using multiple locations to use a single location, including the option of no longer using any of its original locations.
• Reorganize a table that is using multiple locations to extend over a different number of locations, including the option of no longer using one or more of the original locations.
Afterwards, the table is reorganized to spread equally, in sized blocks, over the specified locations.
Last modified date: 08/14/2024