15.10 - Example: Creating Column-Partitioned Sparse Join Index With Autocompression Based on the Default - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

This example uses the following base table.

     CREATE TABLE acd_t1 (
       a INTEGER, 
       b INTEGER, 
       c INTEGER, 
       d INTEGER) 
     PRIMARY INDEX(a);

You create the following column-partitioned sparse join index on t1.

     CREATE JOIN INDEX j_acd_t1 AS 
       SELECT ROWID AS rw, a, b 
       FROM acd_t1 
       WHERE a < 10
     PARTITION BY COLUMN;

This defines a column-partitioned join index where each column is contained within its own column partition and with system-determined COLUMN format.

Because neither AUTO COMPRESS nor NO AUTO COMPRESS is specified in the join index definition, whether the partitions have autocompression or not depends on the value of the AutoCompressDefault cost profile constant as indicated by the following table.

IF the value of AutoCompressDefault is … THEN the partitions …
1 use autocompression.
2 do not use autocompression.

For more information about the AutoCompressDefault cost profile constant, see SQL Request and Transaction Processing, B035-1142.