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

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

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.