comrpess column

Tools
Enthusiast

comrpess column

Does the compressed column play an impact in query execution, when it is involved in a left outer join?
the column is compressed to a value for eg: '$'

-Thank you
3 REPLIES
Enthusiast

Re: comrpess column

column compression generally increase query performance
Enthusiast

Re: comrpess column

There is always performance degrade whenever any column is compressed and doesnt matter as used in which kind of join(left, right, inner etc). However, compression is used for space saving.
Enthusiast

Re: comrpess column

This could be true for other databases, but not in case of Teradata, since when compression is applied there is less physical I/O required for queries, as more rows can fit into one data block. It also reduces storage cost. It does not require additional CPU for compress/uncompress. Teradata implement compression very differently from other databases. It stores compressed values in the table header row and table header is copied on each AMP. If fact, rows remain compressed, when feteched into memory.
Also, Teradata compression is completely transparent to applications, utilities, queries, and views.

So generally speaking, compression provides significant performance improvement for general ad-hoc workloads and full-table scan applications.