DWHPro Insights

Teradata Columnar Solution: Advantages and Disadvantages

By Roland Wenzlofsky ·

Teradata Columnar Solution: Advantages and Disadvantages

How Advanced Is The Teradata Columnar Solution?

A Column Store is defined by three distinct properties:

  • Improved compression compared to Row Stores.

    This results from considerably better compression rates that we can achieve by packing columns into data blocks. Since the values of a column come from a domain, the entropy is much lower.
  • Columnar projection

    Only the columns that are required are read.
  • Column storage, materialization and vector processing are engine/release-specific. Choose ROW/COLUMN formats and DML strategy from the actual workload; neither format nor a fixed column count guarantees superior performance. This article did not benchmark SIMD, materialization or DML behavior.

Why Is The Teradata Solution Great Even Though It Does Not Offer Columnar Processing?

Assuming a standard query, a date range selection is frequently made with a WHERE condition imposing a date restriction.

However, only a portion of the table’s current rows are frequently necessary.

Teradata offers simultaneous column and row partitions.

Column storage, materialization and vector processing are engine/release-specific. Choose ROW/COLUMN formats and DML strategy from the actual workload; neither format nor a fixed column count guarantees superior performance. This article did not benchmark SIMD, materialization or DML behavior.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

The Advantages Of Teradata Columnar

  • Column storage, materialization and vector processing are engine/release-specific. Choose ROW/COLUMN formats and DML strategy from the actual workload; neither format nor a fixed column count guarantees superior performance. This article did not benchmark SIMD, materialization or DML behavior.
  • Significant compression due to low entropy of column values (Dictionary Compression, Run-Length Encoding)

But storing in COLUMN format also has disadvantages since Teradata must search for the desired Index for a specific row in the column.

Therefore Teradata offers the ROW format as an alternative. Here the ROWID is stored with each column value. This allows Teradata to access the value directly as usual.

ROW Or COLUMN Format?

The following rules apply:

Primary Index Or Primary AMP Index Access

Teradata’s hybrid solution offers the added benefit of accessing data through both the Primary Index and Primary AMP Index. The former enables direct row access through hashing, while the latter restricts processing to only the necessary AMPs.

Should I Use From Now On Only Partitioned Column Tables?

What Is The Biggest Disadvantage Of The Teradata Solution For Column Partitioning?

Creating tables presents numerous possibilities, which can be enticing. However, it necessitates a considerable amount of expertise to optimize its performance. Common inquiries include:

  • Row partitioning, column partitioning, or both?
  • Column partitioning using ROW or COLUMN format?
  • Maybe indexing with USI, NUSI, or Join Index is better?
  • Column partitioning with NOPI, Primary Index, or Primary AMP Index?

Final Considerations

Optimizing a Teradata system can greatly enhance its performance, though it necessitates considerable expertise due to its complexity.

Cloud elasticity does not eliminate optimization. SQL, pruning, data layout and compute configuration affect cost and latency; Snowflake standard and hybrid tables also have different designs.

Ultimately, you exchange capital expenditures for operational ones by considering the total cost of ownership.

Be Careful With The Cloud

My primary concern regarding cloud databases is the uncertainty surrounding the longevity of their pricing models, such as billing by the minute. The evolution of mobile tariffs and the resourcefulness of operators serve as cautionary examples.

Check out this excellent piece on Teradata’s columnar technology.

https://support.teradata.com/community?id=community_blog&sys_id=a2a893671b9bfb00682ca8233a4bcb95

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam