16.20 - Example: Creating Column-Partitioned Sparse Join Index With Autocompression Based on the Default - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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.