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.
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.
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.
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: