17.10 - Example: Creating a Join Index with a Sparse Map - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

This example uses the following customer and orders tables.

CREATE TABLE customer (
c_custkey INTEGER,
c_name CHARACTER(26) not null,
c_address VARCHAR(41),
c_nationkey INTEGER,
c_phone CHARACTER(16),
c_acctbal DECIMAL(13,2),
c_mktsegment CHARACTER(21),
c_comment VARCHAR(127))
PRIMARY INDEX( c_custkey );
CREATE TABLE orders (
o_orderkey INTEGER,
o_date DATE FORMAT 'yyyy-mm-dd',
o_status CHARACTER(1),
o_custkey INTEGER,
o_totalprice DECIMAL(13,2),
o_orderpriority CHARACTER(21),
o_clerk CHARACTER(16),
o_shippriority INTEGER,
o_comment VARCHAR(79))
UNIQUE PRIMARY INDEX(o_orderkey);

This statement creates a row-compressed join index using a sparse map.

CREATE JOIN INDEX ord_cust_idx, MAP=SmallTableMap AS
SELECT (o_custkey, c_name), (o_status, o_date, o_comment)
FROM orders, customer
WHERE o_custkey = c_custkey;