15.10 - Join Index - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K
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 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.

  • If the AutoCompressDefault cost profile is set to 0, neither AUTO COMPRESS nor NO AUTO COMPRESS follows COLUMN in the report.
  • If the AutoCompressDefault cost profile is set to 1, AUTO COMPRESS follows COLUMN in the report.
  • If the AutoCompressDefault cost profile is set to 2, NO AUTO COMPRESS follows COLUMN in the report.

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.

For more information about the AutoCompressDefault cost profile, see SQL Request and Transaction Processing, B035-1142.

Example: SHOW JOIN INDEX

This example illustrates a SHOW JOIN INDEX request run against the join index named ord_cust_idx.

     SHOW JOIN INDEX ord_cust_idx;
     *** Text of DDL statement returned.
     *** Total elapsed time was 1 second.
     ----------------------------------------------------
     CREATE JOIN INDEX user
.ord_cust_idx AS
      SELECT user
.customer.c_custkey, user
.customer.c_name,
      user
.orders.o_status, user
.orders.o_date, user
.orders.o_comment
      FROM user.orders, user.customer
      WHERE user.orders.o_custkey = user.customer.c_custkey
     PRIMARY INDEX (c_custkey)
     INDEX ( o_date );

Example: SHOW JOIN INDEX in XML Format

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 definition for join index ji5_1 looks like this.

    CREATE JOIN INDEX user.ji5_1 AS 
      SELECT COUNT(*)(FLOAT, NAMED CountStar), 
          user_name.ct5_1.ca1 AS a3,
          user_name.ct5_1.cb1 AS b3, COUNT(user_name.ct5_1.cc1 )
          (FLOAT, AS c3 ),
          SUM(user.ct5_1.ce1 - user.ct5_2.ce2 )(DECIMAL(38,4), NAMED d3,
          EXTRACT(YEAR FROM (user.ct5_2.cb2 ))AS e3
      FROM user.ct5_1, user.ct5_2
      WHERE (user.ct5_1.ca1 = user.ct5_2.ca2) 
      AND  (user.ct5_1.cd1 <> '000002')
      GROUP BY user.ct5_1.ca1 AS a3, user.ct5_1.cb1 AS b3,
          EXTRACT(YEAR FROM (user.ct5_2.cb2)) AS e3
    PRIMARY INDEX (a3);

When you submit the following SHOW IN XML JOIN INDEX request, Teradata Database returns the report that follows the request.

    SHOW IN XML JOIN INDEX ji5_1;
XML Document for SHOW IN XML JOIN INDEX ji5_1:
<?xml version="1.0" encoding="utf-8"?>
<TeradataDBObjectSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://schemas.teradata.com/TeradataDBObject TeradataDBObject.xsd"
xmlns="http://schemas.teradata.com/TeradataDBObject" version="1.0">
<JoinIndex objId="0:332" objVer="2" name="ji5_1" dbName="testdb" fallback="true" checkSumLevel="on">
<FixedColumnList>
<Column name="CountStar" order="1">
<DataType><Float/></DataType>
</Column>
<Column name="a3" order="2">
<DataType><Integer/></DataType>
<Constraint nullable="false"/>
</Column>
<Column name="b3" order="3">
<DataType><Integer/></DataType>
</Column>
<Column name="c3" order="4">
<DataType><Float/></DataType>
</Column>
<Column name="d3" order="5">
<DataType><Decimal precision="38" scale="4"/></DataType>
</Column>
<Column name="e3" order="6">
<DataType><Integer/></DataType>
</Column>
</FixedColumnList>
<RefList>
<Ref name="ct5_1" dbName="testdb" type="table"/>
<Ref name="ct5_2" dbName="testdb" type="table"/>
</RefList>
<SQLText>
<![CDATA[
CREATE JOIN INDEX user.ji5_1 ,FALLBACK ,CHECKSUM = ON AS
SELECT 
COUNT(*)(FLOAT, NAMED CountStar),
user.ct5_1.ca1 AS a3 ,
user.ct5_1.cb1 AS b3 ,
COUNT(user.ct5_1.cc1 )(FLOAT, AS c3),
SUM(user.ct5_1.ce1 - user.ct5_2.ce2)(DECIMAL(38,4), AS d3),
EXTRACT(YEAR FROM (user.ct5_2.cb2)) AS e3 
FROM user.ct5_1 ,user.ct5_2
WHERE (user.ct5_1.ca1 = user.ct5_2.ca2 ) AND  (user.ct5_1.cd1 <>  '
000002')
GROUP BY user.ct5_1.ca1 AS a3 ,user.ct5_1.cb1 AS b3 ,
EXTRACT(YEAR FROM (user.ct5_2.cb2)) AS e3 
PRIMARY INDEX (a3);
   </SQLText>
</JoinIndex>
<Environment>
  <Server dbRelease="14.10.00.00" dbVersion="14.10.00.06" hostName="testhost"/>
  <User userId="0" userName="dbc"/>
  <Session charset="utf8" dateTime="2012-01-01T14:00:00-08:00"/>
</Environment>
</TeradataDBObjectSet>