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

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, partitions will have autocompression, which is the default.