15.00 - Selecting Rows From a Column-Partitioned Table - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Selecting Rows From a Column‑Partitioned Table

Without an index being defined on a column‑partitioned table, any SELECT request on the table results in at least one full‑column partition scan up to a full-table scan, which can be expensive if there are not at least as many available column partition contexts as column partitions that need to be accessed.

Because you can define USIs, NUSIs, and join indexes on a column‑partitioned table, you can implement indexing methods that are appropriate to facilitate the performance of your applications. Compared to queries that are unable to use a primary index for access even if a table has a primary index, column-partitioning a table can facilitate far better performance for queries on that table over a full‑table scan when not all of the column partitions need to be accessed.

Because Teradata Database supports fallback for column‑partitioned tables, you can view a column‑partitioned table that has fallback when there are down AMPs on your system.

Consider the following when selecting data from a column‑partitioned table.

  • Without a primary index, there is no single-AMP, primary index access path; therefore, at least one full‑column partition scan must be performed on all AMPs unless the Optimizer chooses a secondary index or join index access path.
  • The best practice is that if you need a high transaction rate for queries run against a table, a column‑partitioned table without indexes is probably not a good choice.

  • Secondary indexes are valid for column‑partitioned tables, and secondary index access paths for SELECT requests work the same for a column‑partitioned table as they do for a primary‑indexed table.
  • Retrieving the needed column partition values can be more expensive than retrieving a row using a secondary index. For example, if n column partition values are needed for a query result set, the number of I/O operations needed to retrieve those rows could be n times as many as would be required for a primary‑indexed table, and even higher if the primary‑indexed table can take better advantage of the FSG cache.

  • You should design the queries you intend to run on your column‑partitioned tables to have one to a few predicates that are very selective in combination, and the number of available column partition contexts should be at least equal to the number of accessed column partitions.
  • Avoid using unselective queries that retrieve most or all the columns from a column‑partitioned table.
  • For example, the following SELECT request can be very expensive if there are not enough available column partition contexts for cp_table and there is not an alternative access method such as a join index without column partitioning:

         SELECT * 
         FROM cp_table;