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

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

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>