Column-Partitioned Table as a Source Table in a Data Moving Request
Column‑partitioned tables are not usually expected to be used as source tables; however, there might be situations where a column‑partitioned table is the source for an insert.
Operations such as INSERT … SELECT, MERGE, UPDATE … FROM, and DELETE … FROM for a target PI table can run slower when the source is a column‑partitioned table compared to the case when a source primary‑indexed table with the same primary index as the target.
This is because with a column‑partitioned source table, selected rows must be constructed from the referenced columns of the column partitions, redistributed, and sorted locally on the AMPs. Copying data from one primary‑indexed table to another primary‑indexed table with a different primary index or different partitioning requires redistribution and AMP‑local sorting.
Operations such as INSERT … SELECT, MERGE, UPDATE … FROM, and DELETE … FROM for a target NoPI table can run slower when the source is a column‑partitioned table compared to the case when the source table is either a nonpartitioned NoPI table or a primary‑indexed table.
This is because with a column‑partitioned source table, selected rows must be reconstructed from the referenced columns of the column partitions.
Operations such as INSERT … SELECT, MERGE, UPDATE … FROM, and DELETE … FROM for a target column‑partitioned table can run slower when the source is a column‑partitioned table compared to the case when the source table is either a NoPI or a primary‑indexed table.
This is because with a source column‑partitioned table, selected rows might need to be constructed from the referenced columns of the column partitions and often require spooling the source column‑partitioned table to reconstruct rows followed by reading the rows and converting to the column partitioning of the target column‑partitioned table.
In some cases when the source and target have the same column definitions and partitioning, spooling can be avoided by a direct copy of the source table to the target table based on physical rows.
In some cases, even if the tables have different column partitioning and there are sufficient available column-partition contexts, spooling can be avoided.