Example: Creating Column-Partitioned Sparse Join Index With Autocompression Based on the Default - Teradata Vantage - Analytics 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.