The following tables list the operations and utilities that you may consider using with column-partitioned tables. The tables include information such as whether the utility or operation is supported for column-partitioned tables, possible substitutions for utilities and operations that Vantage does not support for column-partitioned tables, and usage information about the operation or utility with respect to nonpartitioned NoPI tables.
The first table lists the SQL operations you may use for column-partitioned tables.
| SQL Statement Name | Support for Column-Partitioned Tables | Usage Notes |
|---|---|---|
| DELETE | Supported | See Column Partitioning Performance. |
| INSERT | Supported | While INSERT operations into column-partitioned tables are supported, do not use single-row INSERT requests to add rows to column-partitioned tables frequently because such requests can cause a large degradation in performance by needing to transform a row into a column and then appending a column partition value to each of the column partitions. |
| INSERT ... SELECT | Supported | |
| MERGE | Not supported | The UPDATE component of a MERGE request is required to fully specify the primary index. Vantage does not support MERGE requests for NoPI and column-partitioned tables. Use UPDATE and INSERT requests instead. |
| UPDATE | Supported | See Column Partitioning Performance. |
| UPDATE (Upsert Form) | Not supported | The UPDATE component of an Upsert request is required to fully specify the primary index. Vantage does not support UPSERT requests for NoPI and column-partitioned tables. |
The second table lists the Teradata Tools and Utilities operations you may use for column-partitioned tables.
| Utility Name | Support for Column-Partitioned Tables | Usage Notes |
|---|---|---|
| FastExport | Supported | You can export the data in NoPI tables and column-partitioned tables the same way you can export data rows for a primary-indexed table |
| FastLoad | Not supported | Use INSERT ... SELECT or Teradata Parallel Data Pump requests instead. These are typically used to populate a column-partitioned table. You can also use FastLoad to load data into a staging table and then use an INSERT ... SELECT request to populate the column-partitioned table |
| MultiLoad | Not supported | Use INSERT ... SELECT and Teradata Parallel Data Pump requests instead. These are typically used to populate a column-partitioned table. |
| Reconfiguration | Supported | Reconfiguration processing for a NoPI table or column-partitioned table is similar to the Reconfiguration processing that is done for a primary-indexed table. The main difference is that a NoPI table or column-partitioned table typically has one hash bucket per AMP, which is like a skewed NUPI table. Therefore, when you reconfigure a system to have more AMPs, there may be AMPs that do not have any data for a NoPI table or column-partitioned table. Therefore, Reconfiguration can cause data skewing for both NoPI and column-partitioned tables. |
| Restore and Copy | Supported | Restore and Copy processing for a NoPI or column-partitioned table are similar to the processing used by those utilities for a primary-indexed table. The main difference is that a NoPI table or column-partitioned table typically has one hash bucket per AMP, which is like a nonunique NUPI table. Therefore, when you restore or copy the data from a NoPI table to a different configuration that has more AMPs, there may be AMPs that have no data. Therefore, Restore and Copy can cause can cause data skewing for both NoPI and column-partitioned tables. |