This section summarizes the benefits, best uses, and restrictions of temperature-based block-level compression in Teradata Database.
Benefits of Temperature-Based Block-Level Compression
- Controlled by Teradata Virtual Storage
- Can specify for all user data tables, or can apply it at the level of individual tables
When applied to an individual table, impacts all compressible subtables and structures associated with that table.
When applied at the level of individual tables, specified compression is inherited by all of the compressible subtables associated with that table, including the following:
- Primary data tables
- Fallback data tables
- Index fallback subtables
- CLOB subtables
- Can use query bands in addition to automatic compression to manage the compression status of tables under the control of temperature-based block-level compression
- Can use query bands to load temperature-sensitive tables in either compressed or uncompressed format if you want a uniform compression state and you know the expected temperature of the data being loaded
For more information about using query bands with temperature-based block-level compression, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .
- Has no effect on table header size
Best Use of Temperature-Based Block-Level Compression
- Large tables where a WARM or HOT subset of their data is accessed frequently
- Tables whose temperature profile is relatively stable across time
- Large row-partitioned tables whose activity is concentrated in currently WARM or HOT row partitions, but whose older row partitions are relatively dormant and COLD
- Column-partitioned tables because their frequently accessed data is grouped together in columnar partitions that are WARM or HOT and other column partitions that are relatively dormant and COLD
Restrictions and Limitations of Temperature-Based Block-Level Compression
- Only applies to cylinders containing permanent tables
- Can make the data have a warmer temperature than it had when it was uncompressed because after compression and cylinder consolidation, each cylinder contains more data than it previously had
- Because Teradata Virtual Storage measures temperature at the cylinder level, a greater number of data blocks could mean a higher cylinder access count