16.20 - Join Index - 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
join_index_name

The name of the join index whose most recent SQL create text is to be reported.

If the join index is defined on a temporal table, the SQL text returned by SHOW JOIN INDEX includes the temporal qualifier that was associated with the embedded SELECT request at the time the join index was created, whether it was explicitly specified or implicitly derived from the default session temporal qualifier.

If the join index is system-defined, the SQL text returned by SHOW JOIN INDEX displays the non-reserved keyword SYSTEM_DEFINED between the words CREATE and JOIN.

If you submit this SQL text as a CREATE JOIN INDEX request, Teradata Database returns an error to the requestor because SYSTEM_DEFINED is not valid SQL text.

A SHOW JOIN INDEX IN XML request does not report all of the definition constructs for join indexes, so it is not possible to decompose and reconstruct their definitions from their reported XML format definitions.

However, the XML text for join index definitions is helpful because it includes the following useful information:
  • The names and data types of the columns in the join index definition.
  • A list of all of the referenced database objects in the join index definition.

For further information, see Teradata Vantage™ SQL Data Definition Language Detailed Topics , B035-1184 .

For a column-partitioned join index, the SQL text returned by SHOW JOIN INDEX includes a PARTITION BY clause with a COLUMN clause. Grouping, if any, is included in the COLUMN clause, not in the select expression list.

See table_name later in this table for a list of exceptions to the rules for what Teradata Database reports for a SHOW JOIN INDEX request. These rules apply equally to SHOW JOIN INDEX and SHOW TABLE requests.

Example: SHOW JOIN INDEX

This example illustrates a SHOW JOIN INDEX statement for the join index ord_cust_idx.

     SHOW JOIN INDEX ord_cust_idx;
The statement reports the DDL for the join index:
     CREATE JOIN INDEX YourDB.ord_cust_idx ,NO FALLBACK 
,CHECKSUM = DEFAULT, MAP = SmallTableMap COLOCATE USING YourDB_ord_cust_idx AS 
SELECT (YourDB.orders.o_custkey ,YourDB.customer.c_name )
,(YourDB.orders.o_status ,
YourDB.orders.o_date ,YourDB.orders.o_comment )
 FROM YourDB.orders ,YourDB.customer 
WHERE YourDB.orders.o_custkey =  YourDB.customer.c_custkey 
PRIMARY INDEX ( o_custkey )

Example: SHOW JOIN INDEX in XML Format

This example provides an example of the output for the following statement:

    SHOW IN XML JOIN INDEX ord_cust_idx;

The XML document generated by SHOW IN XML JOIN INDEX does not contain the parsed query for the join index, only the column definitions of the index and the database objects it references. As a result, this XML document cannot be used to recreate the join index. However, it can be used to create a table having the same column definition of the original join index. The statement returns the following report:

    <?xml version="1.0" encoding="UTF-8" standalone="no" ?><TeradataDBObjectSet version="1.0" xmlns="http://schemas.teradata.com/dbobject" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.teradata.com/dbobject http://schemas.teradata.com/dbobject/DBObject.xsd"><JoinIndex checkSumLevel="Default" colocateName="YourDB_ord_cust_idx" dbName="YourDB" fallback="false" map="SmallTableMap" map_kind="sparse" name="ord_cust_idx" objId="0:3652" objVer="1">
<RepeatComlumnList><Column format="X(1)" name="o_status" nullable="true" order="1"><DataType><Char casespecific="true" charset="LATIN" length="1" uppercase="false" varying="false"/></DataType></Column>
<Column format="yyyy-mm-dd" name="o_date" nullable="true" order="2"><DataType><Date/></DataType></Column>
<Column format="X(79)" name="o_comment" nullable="true" order="3"><DataType><Char casespecific="true" charset="LATIN" length="79" uppercase="false" varying="true"/></DataType></Column></RepeatComlumnList>
<FixedColumn
List><Column format="-(10)9" name="o_custkey" nullable="true" order="1"><DataType><Integer/></DataType></Column><Column format="X(26)" name="c_name" nullable="false" order="2"><DataType><Char casespecific="true" charset="LATIN" length="26" uppercase="false" varying="false"/></DataType></Column></FixedColumnList>
<RefList><Ref dbName="YourDB" name="orders" type="Table"/><Ref dbName="YourDB" name="customer" type="Table"/></RefList>
<SQLText><![CDATA[CREATE JOIN INDEX YourDB.ord_cust_idx ,NO FALLBACK 
,CHECKSUM = DEFAULT, MAP = SmallTableMap COLOCATE USING YourDB_ord_cust_idx AS 
SELECT (YourDB.orders.o_custkey ,YourDB.customer.c_name )
,(YourDB.orders.o_status ,
YourDB.orders.o_date ,YourDB.orders.o_comment )
 FROM YourDB.orders ,YourDB.customer 
WHERE YourDB.orders.o_custkey =  YourDB.customer.c_custkey 
PRIMARY INDEX ( o_custkey )
INDEX ( o_custkey ,c_name )
INDEX ( o_status ,o_date ,o_comment );]]></SQLText></JoinIndex><Environment><Server dbRelease="16.10" dbVersion="16.10" hostName="localhost"/><User userId="00000704" userName="YourDB"/><Session charset="UTF8" dateTime="2017-03-24T16:54:56"/></Environment></TeradataDBObjectSet>