16.20 - Example: Creating a Join Index with a Sparse Map - 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 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;