Table Cloning
Cloning a table is functionally equivalent to creating a full copy of an X100 table. A table owner or any user having "select" privilege on the "source table" and "create table" permission in the database, can create a clone.
Note: To create multiple clones of different tables in a single statement, you need select privilege on every source table you want to clone.
After cloning a table, they are two separate independent tables. Subsequent modifications or alterations to one table will not affect the other. The cloned table will have the same column definitions, contents, locations, partition, configuration, and statistics as the original table.
Cloning differs from copying in the following aspects:
• Cloning a table is faster because the underlying storage blocks are not copied.
• When a table is cloned, it uses less storage space until the storage blocks are rewritten for either of the tables.
Note: Cloning the contents of the tables works only on X100 tables. On Actian X, metadata-only cloning of Ingres tables are allowed.
The Table Cloning statement has the following format, containing one or more pairs of destination (new) and source (existing) tables:
CREATE TABLE [IF NOT EXISTS]
newtable1 CLONE existingtable1 [, newtable2 CLONE existingtable2, ... ]
[ WITH option, option, ... ];
IF NOT EXISTS
Creates the table if it does not exist and returns without error if the table already exists. The statement has no effect if the new table already exists.
If this option is specified in a clone statement containing multiple destination-source pairs:
• If all the destination tables already exist, the statement has no effect.
• If none of the destination tables already exist, the statement runs as normal.
• If some destination tables exist and some do not exist, this is an error.
Where IF NOT EXISTS is not given, an error occurs if any destination table already exists.
Last modified date: 08/14/2024