- 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, the 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 the database reports for a SHOW JOIN INDEX request. These rules apply equally to SHOW JOIN INDEX and SHOW TABLE requests.