17.10 - Example: Using the BLOCKCOMPRESSION Reserved Query Band - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

These examples show how to use the BLOCKCOMPRESSION reserved query band to set the block-level compression characteristics for various tables, subtables, or both for a session or transaction.

Set the BlockCompression query band value to ALL or YES to compress all new subtables loaded for the session or transaction.

These examples set the query band for the current session.

The following statements are equivalent:

     SET QUERY_BAND = 'BLOCKCOMPRESSION=YES;' FOR SESSION;
     SET QUERY_BAND = 'BLOCKCOMPRESSION=ALL;' FOR SESSION;

Setting the BlockCompression query band value to FALLBACK compresses all new fallback subtables, including the fallback copy of eligible LOB subtables, loaded for the session or transaction.

This example sets the query band for the current session:

     SET QUERY_BAND = 'BLOCKCOMPRESSION=FALLBACK;' FOR SESSION;

Set the BlockCompression query band value to NO or NONE to not compress any new subtables loaded for the session or transaction.

These examples set the query band for the current session.

The following statements are equivalent:

     SET QUERY_BAND = 'BLOCKCOMPRESSION=NO;' FOR SESSION;
     SET QUERY_BAND = 'BLOCKCOMPRESSION=NONE;' FOR SESSION;

Set the BlockCompression query band value to ONLYCLOBS to compress all new primary and fallback LOB subtables that are eligible for compression in the session or transaction. Data loaded into all other new subtables during the session or transaction is not compressed.

This example sets the query band for the current transaction.

     BEGIN TRANSACTION
       SET QUERY_BAND = 'BLOCKCOMPRESSION=ONLYCLOBS;' FOR TRANSACTION;
           …
     END TRANSACTION

The ellipsis represents the SQL request set that causes the data load to occur.

Setting the BLOCKCOMPRESSION query band value to WITHOUTCLOBS compresses all new primary and fallback subtables loaded for the session or transaction except for LOB subtables, whose data is not compressed.

This example sets the query band for the current session.

     SET QUERY_BAND = 'BLOCKCOMPRESSION=WITHOUTCLOBS;' FOR SESSION;