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

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;