16.20 - Example: Create Join Index - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ Data Dictionary

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
zzu1512081120577.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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).