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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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;