Social Icons

Teradata Columnar

TeradataWiki-Teradata_columnar
Teradata Columnar or Column Partitioning (CP) is a new physical database design implementation option (starting with Teradata 14.0) that allows single columns or sets of columns of a NoPI table to be stored in separate partitions.  Column partitioning can also be applied to join indexes.

Columnar is simply a new approach for organizing the data of a user-defined table or join index on disk.

Columnar offers the ability to partition a table or join index by column.  It can be used alone or in combination with row partitioning in multilevel partitioning definitions.  Column partitions may be stored using traditional 'ROW' storage or alternatively stored using the new 'COLUMN' storage option.  In either case, columnar can automatically compress physical rows where appropriate.  

The key benefit in defining row-partitioned (PPI) tables is when queries access a subset of rows based on constraints on one or more partitioning columns.  The major advantage of using column partitioning is to improve the performance of queries that access a subset of the columns from a table, either for predicates (e.g., WHERE clause) or projections (i.e., SELECTed columns).

Because sets of one or more columns can be stored in separate column partitions, only the column partitions that contain the columns needed by the query need to be accessed.  Just as row-partitioning can eliminate rows that need not be read, column partitioning eliminates columns that are not needed.

The advantages of both can be combined, meaning even less data moved and thus reduced I/O.  Fewer data blocks need to be read since more data of interest is packed together into fewer data blocks.  

Columnar makes more sense in CPU-rich environments because CPU cycles are needed to "glue" columns back together into rows, for compression and for different join strategies (mainly hash joins).

Benefits of using the Teradata Columnar:
  • Improved query performance
  • Reduced disk space
  • Increased flexibility
  • Reduced I/O
  • Ease of use

3 comments:

  1. Hi, Can we create/delete CP in lifetime of table or it can only be created on table creation and deleted upon table deletion?

    ReplyDelete
    Replies
    1. It can be created by the time of table creation. Syntax below

      Create Table EMP_COLUMNAR
      (
      Emp_Num integer
      ,Dept_Num integer
      ,First_Name Varchar(10)
      ,Last_Name Varchar(10)
      ,Salary decimal(10,2)
      )
      No Primary Index
      Partition By Column;

      Delete
  2. Why the table needs to be a NopI, for a column partitioning? any reason in specific?

    ReplyDelete