15.00 - Operations and Utilities for Column-Partitioned Tables - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Operations and Utilities for Column‑Partitioned Tables

The following tables provide a comprehensive list of the operations and utilities that you might 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 Teradata Database does not support for column-partitioned tables, and various usage information about the operation or utility as it applies to nonpartitioned NoPI tables.

The first table lists the SQL operations you might use for column‑partitioned tables.

 

    SQL Statement Name

 

      Support for Column‑Partitioned          Tables

                                            Usage Notes

 

DELETE

Supported

See “Deleting Rows From a Column‑Partitioned Table” on page 323.

INSERT

Supported

While INSERT operations into column‑partitioned tables are supported, you should 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.

See “Bulk Loading a Column‑Partitioned Table” on page 318.

INSERT … SELECT

Supported

See “Bulk Loading a Column‑Partitioned Table” on page 318.

MERGE

Not supported

The UPDATE component of a MERGE request is required to fully specify the primary index.

Because column‑partitioned and nonpartitioned NoPI tables do not have a primary index, Teradata Database cannot support MERGE requests for NoPI tables.

Use UPDATE and INSERT requests instead.

UPDATE

Supported

See “Updating a Column‑Partitioned Table” on page 323.

UPDATE (Upsert Form)

Not supported

The UPDATE component of an Upsert request is required to fully specify the primary index.

Because column‑partitioned and nonpartitioned NoPI tables do not have a primary index, Teradata Database cannot support UPSERT requests for NoPI tables.

The second table lists the Teradata Tools and Utilities operations you might use for and column‑partitioned tables.

 

TTU Utility Name

 

      Support for Column‑Partitioned          Tables

                                                         Usage Notes

 

CheckTable

Supported

The LEVEL HASH check, which is done with either an explicit LEVEL HASH command or implicitly in a LEVEL THREE command, works differently on a primary‑indexed table and a NoPI or column‑partitioned table.

  • For a primary‑indexed table, the check regenerates the row hash for each data row based on the primary index values and then compares with the rows on disk.
  • For NoPI tables and column‑partitioned tables, the check looks at the row hash value for each data row and verifies that the hash bucket that is part of the row hash correctly belongs to the AMP.
  • 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 normally 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 normally 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 normally has one hash bucket per AMP, which is like a very skewed non‑unique NUPI table.

    Because of this, when you reconfigure a system to have more AMPs, there might be some AMPs that do not have any data for a NoPI table or column‑partitioned table.

    As a result, 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 very 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 normally has one hash bucket per AMP, which is like a very nonunique NUPI table.

    Because of this, when you restore or copy the data from a NoPI table to a different configuration that has more AMPs, there might be some AMPs that do not have any data.

    As a result, Restore and Copy can cause can cause data skewing for both NoPI and column‑partitioned tables.

    TableRebuild

    Supported

    Table Rebuild processing for a NoPI or column‑partitioned table is the same as the Table Rebuild processing for a primary‑indexed table.

    The table must have fallback protection for Table Rebuild to be able to rebuild it. Rows in a NoPI table or column‑partitioned table have a row hash value, so Teradata Database can rebuild them the same way it rebuilds rows for a primary‑indexed table.