You can only change a distribution key on tables in a
single database partition.
About this task
First drop the existing
distribution key, and then create another.
To change distribution
keys using the Control Center:
- Open the Alter Table notebook: From the Control Center, expand
the object tree until you find the Tables folder. Click the Tables folder.
Any existing tables are displayed in the pane on the right side of
the window. Right-click the table you want and select Alter from
the pop-up menu. The Alter Table notebook opens.
- On the Keys page, select a distribution key in the table and click Change.
The Change Distribution Key window opens.
- Select the columns that you want to add as distribution key columns
and move them to the Selected columns box.
To change distribution keys using the command line, use
the DROP DISTRIBUTION option of the ALTER TABLE statement. For example,
the following SQL statement drops the distribution key MIX_INT from
the MIXREC table:
ALTER TABLE MIXREC
DROP DISTRIBUTION
You cannot change the distribution
key of a table spanning multiple database partitions. If you try to
drop it, an error is returned.
To change the distribution key
of multiple database partitions, either:
To change the distribution
key of multiple database partitions, either:
- Export all of the data to a single database partition and then
follow the above instructions.
- Export all of the data, drop the table, recreate the table redefining
the distribution key, and then import all of the data.
Neither of these methods are practical for large databases;
it is therefore essential that you define the appropriate distribution
key before implementing the design of large databases.