15.10 - Hash 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
hash_index_name
The name of the hash index whose most recent SQL create text is to be reported.

Example: SHOW HASH INDEX

This example illustrates a SHOW HASH INDEX request run against the hash index named ord_hidx created by the following CREATE HASH INDEX request.

     CREATE HASH INDEX ord_hidx (o_orderdate) ON orders;
     SHOW HASH INDEX ord_hidx;
      *** Text of DDL statement returned.
      *** Total elapsed time was 1 second.
     ------------------------------------------------------------
     CREATE HASH INDEX PLS.ord_hidx, NO FALLBACK PROTECTION      (user.orders.o_ordertable) ON user.orders;

The report differs from the text of the CREATE HASH INDEX DDL in several ways.

  • All names are qualified
  • The fallback protection option is displayed

Note that when you do not specify a BY or ORDER clause in the CREATE HASH INDEX DDL, the default values for those clauses are not reported.

Example: SHOW HASH INDEX in XML Format

This example returns the create text for hash index ord_hidx in XML format. As is true for a SHOW IN XML TABLE request, the XML document generated by a SHOW IN XML HASH INDEX request reports all of the information required to recreate the hash index.

Assume you create the following hash index.

     CREATE HASH INDEX user.ord_hidx, NO FALLBACK, CHECKSUM=DEFAULT
                       (o_orderdate) ON user.orders;

A SHOW HASH INDEX IN XML request for hash index ord_hidx returns the following XML document for its create text.

     SHOW HASH INDEX IN XML user.ord_hidx;
<?xml version="1.0" encoding="utf-8"?>
<TeradataDBObjectSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://schemas.teradata.com/TeradataDBObject TeradataTDObject.xsd"
xmlns="http://schemas.teradata.com/TeradataDBObject">
<HashIndex name="ord_hidx" dbName="user" tableName="orders "fallback="false" checkSumLevel="default">
<IndexColumnList>
<Column name="o_orderdate" order="1"/>
</IndexColumnList>
<SQLText>
<![CDATA[
CREATE HASH INDEX user.ord_hidx ,NO FALLBACK ,CHECKSUM = DEFAULT
(o_orderdate )
ON user.orders ;
</SQLText>
</HashIndex>
    <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>