Example: SHOW JOIN INDEX in XML Format - Advanced SQL Engine - Teradata Database

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>