17.00 - Example: Create Join Index - Teradata Database

Teradata Vantage™ - Data Dictionary

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Administration
Programming Reference
featnum
B035-1092-170K

The following statement results in DBC.IndicesV[X].IndexType of 1 and 2:

CREATE JOIN INDEX BB_OTB_DATA.ORDER_JOIN_LINE ,NO FALLBACK ,CHECKSUM = DEFAULT AS
SELECT (BB_OTB_DATA.lineitem.l_orderkey ,BB_OTB_DATA.orders.o_orderdate,
        BB_OTB_DATA.orders.o_custkey ,BB_OTB_DATA.orders.o_totalprice),
       (BB_OTB_DATA.lineitem.l_partkey ,BB_OTB_DATA.lineitem.l_quantity,
        BB_OTB_DATA.lineitem.l_extendedprice,         BB_OTB_DATA.lineitem.l_shipdate )
FROM
(BB_OTB_DATA.lineitem  LEFT OUTER JOIN BB_OTB_DATA.orders  ON
BB_OTB_DATA.lineitem.l_orderkey =  BB_OTB_DATA.orders.o_orderkey )
ORDER BY BB_OTB_DATA.orders.o_orderdate ASC
PRIMARY INDEX ( l_orderkey );

This query results in four rows in DBC.IndicesV[X] with value “1” (the four columns in the first field of the join index) and four rows with value “2” (the four columns in the second field of the join index).