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