Example: Creating Column-Partitioned Join Indexes - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Assume that the following table has been created for this example set.

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

The following CREATE JOIN INDEX requests are equivalent to one another.

This example creates a sparse join index but does not specify an explicit format for the column partition that contains rw. The format for the partition is system-determined to be COLUMN.

     CREATE JOIN INDEX jt1 AS 
       SELECT ROWID AS rw, a, b 
       FROM t1 
       WHERE a < 10
     PARTITION BY COLUMN (rw NO AUTO COMPRESS, ROW(a, b));

This example is identical to the previous example except that it explicitly specifies both COLUMN format for the column partition that contains rw and the optional NO PRIMARY INDEX clause.

     CREATE JOIN INDEX jt1 AS 
       SELECT ROWID AS rw, a, b 
       FROM t1 
       WHERE a < 10
     NO PRIMARY INDEX
     PARTITION BY COLUMN (COLUMN rw NO AUTO COMPRESS, ROW(a, b));

This example does not specify an explicit format for the column partition that contains rw. In this case, the format for the partition that contains rw is system-determined to be COLUMN. The example also adds the optional NO PRIMARY INDEX clause followed by an optional COMMA character to the request in the first example.

     CREATE JOIN INDEX jt1 AS 
       SELECT ROWID AS rw, a, b 
       FROM t1 
       WHERE a < 10
     NO PRIMARY INDEX, 
     PARTITION BY COLUMN (rw NO AUTO COMPRESS, ROW(a,b));

This example is identical to the previous example except that it explicitly specifies COLUMN format for the column partition that contains rw in the partitioning expression and it moves the NO PRIMARY INDEX specification after the partitioning.

     CREATE JOIN INDEX jt1 AS 
       SELECT ROWID AS rw, a, b 
       FROM t1 
       WHERE a < 10
     PARTITION BY COLUMN (COLUMN rw NO AUTO COMPRESS, ROW(a, b)),
     NO PRIMARY INDEX;

This example is identical to the previous example except that it specifies (ROWID AS rw) as a group, specifies NO AUTO COMPRESS for (ROWID AS rw) and it does not specify NO PRIMARY INDEX.

     CREATE JOIN INDEX jt1 AS 
       SELECT (ROWID AS rw) NO AUTO COMPRESS, ROW(a, b)
       FROM t1 
       WHERE a<10
     PARTITION BY COLUMN;

This example is identical to the previous example except that it explicitly specifies NO PRIMARY INDEX before the PARTITION BY clause.

     CREATE JOIN INDEX jt1 AS 
       SELECT (ROWID AS rw) NO AUTO COMPRESS, ROW(a, b)
       FROM t1 WHERE a<10
     NO PRIMARY INDEX 
     PARTITION BY COLUMN;

This example is identical to the previous example except that it specifies a COMMA character between the NO PRIMARY INDEX and PARTITION BY clauses. The COMMA character is required if the preceding item in the index list is a partitioning clause that is not part of an index clause, but is otherwise optional.

     CREATE JOIN INDEX jt1 AS 
       SELECT (ROWID AS rw) NO AUTO COMPRESS, ROW(a, b)
       FROM t1 
       WHERE a<10
     NO PRIMARY INDEX, 
     PARTITION BY COLUMN;

This example is identical to the previous example except that it reorders the placement of the PARTITION BY and NO PRIMARY INDEX clauses from the request in the previous example.

     CREATE JOIN INDEX jt1 AS 
       SELECT (ROWID AS rw) NO AUTO COMPRESS, ROW(a, b) 
       FROM t1 
       WHERE a < 10
     PARTITION BY COLUMN, 
     NO PRIMARY INDEX;

The following set of CREATE JOIN INDEX requests is equivalent to the previous set of CREATE JOIN INDEX requests except that the format for the column partition containing rw is explicitly specified to be COLUMN rather than system-determined to be COLUMN.

     CREATE JOIN INDEX jt1 AS 
       SELECT ROWID AS rw, a, b 
       FROM t1 
       WHERE a<10
     PARTITION BY COLUMN (COLUMN rw NO AUTO COMPRESS, ROW(a,b));
     CREATE JOIN INDEX jt1 AS 
       SELECT ROWID AS rw, a, b 
       FROM t1 
       WHERE a<10
     NO PRIMARY INDEX 
     PARTITION BY COLUMN (COLUMN rw NO AUTO COMPRESS, ROW(a,b));
     CREATE JOIN INDEX jt1 AS 
       SELECT ROWID AS rw, a, b 
       FROM t1 
       WHERE a<10
     NO PRIMARY INDEX, 
     PARTITION BY COLUMN (COLUMN rw NO AUTO COMPRESS, ROW(a,b));
     CREATE JOIN INDEX jt1 AS 
       SELECT ROWID AS rw, a, b 
       FROM t1 
       WHERE a<10
     PARTITION BY COLUMN (COLUMN rw NO AUTO COMPRESS, ROW(a,b)), 
     NO PRIMARY INDEX;
     CREATE JOIN INDEX jt1 AS 
       SELECT COLUMN(ROWID AS rw) NO AUTO COMPRESS, ROW(a, b)
       FROM t1 
       WHERE a<10
     PARTITION BY COLUMN;
     CREATE JOIN INDEX jt1 AS 
       SELECT COLUMN(ROWID AS rw) NO AUTO COMPRESS, ROW(a, b)
       FROM t1 
       WHERE a<10
     NO PRIMARY INDEX PARTITION BY COLUMN;
     CREATE JOIN INDEX jt1 AS 
       SELECT COLUMN(ROWID AS rw) NO AUTO COMPRESS, ROW(a, b)
       FROM t1 
       WHERE a<10
     NO PRIMARY INDEX, 
     PARTITION BY COLUMN;
     CREATE JOIN INDEX jt1 AS 
       SELECT COLUMN(ROWID AS rw) NO AUTO COMPRESS, ROW(a, b)
       FROM t1 
       WHERE a<10
     PARTITION BY COLUMN, 
     NO PRIMARY INDEX;