15.10 - Table Name - 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


Base data table for which to display information.

TEMPORARY
Requested indexes are defined on a materialized global temporary table.
 This keyword is valid only for global temporary tables.
database_name
user_name
Containing database or user for table_name if something other than the current database or user.
table_name
Table on which a report of all primary and secondary indexes is desired.
column_name
Column in a column list for which an index or index list is desired. Use this syntax to exclude primary, secondary, join, and hash indexes defined on columns for which you are not interested.

Example: HELP INDEX on Table Columns

The following example returns information about the specified index or indexes defined on the table columns named cname_1, cname_2, and cname_3.

     HELP INDEX tname (cname_1, cname_2, cname_3);

Example: HELP INDEX on a Table

The following request and response show that one index is defined for the Department table.

     HELP INDEX Personnel.Department;
              Primary
              or
     Unique   Secondary Column Names    Index Id Approximate Count 
     ------   --------- --------------- -------- ----------------- 
     Y        P         DeptNo                 1                 7

Example: Partitioned Primary Index

This example uses the following table definition.

     CREATE TABLE orders (
       o_orderkey      INTEGER NOT NULL,
       o_custkey       INTEGER,
       o_orderstatus   CHARACTER(1) CASESPECIFIC,
       o_totalprice    DECIMAL(13,2) NOT NULL,
       o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_orderpriority CHARACTER(21),
       o_clerk         CHARACTER(16),
       o_shippriority  INTEGER,
       o_comment       VARCHAR(79))
     PRIMARY INDEX OrdPI (o_orderkey)
     PARTITION BY RANGE_N(o_orderdate BETWEEN DATE '1992-01-01'
                                      AND     DATE '1998-12-31'
                                      EACH INTERVAL '1' MONTH)
     UNIQUE INDEX OrdUSI (o_orderkey)
     INDEX Ordx1 (o_orderdate)
     INDEX Ordx2 (o_orderdate) 
      ORDER BY VALUES (o_orderdate)
     INDEX Ordx3 (o_custkey, o_orderdate) 
      ORDER BY VALUES (o_orderdate)
     INDEX Ordx4 (o_custkey, o_orderdate) 
      ORDER BY HASH (o_orderdate)
     INDEX Ordx5 ALL (o_custkey, o_orderstatus) 
      ORDER BY VALUES (o_custkey)
     INDEX Ordx6 ALL (o_custkey, o_orderstatus) 
      ORDER BY HASH (o_custkey)
     INDEX Ordx7 ALL (o_custkey) 
      ORDER BY VALUES (o_custkey)
     INDEX Ordx8 ALL (o_custkey) 
      ORDER BY HASH (o_custkey)
     INDEX Ordx9 (o_shippriority) 
      ORDER BY HASH (o_shippriority)
     INDEX OrdxA ALL (o_clerk) 
      ORDER BY HASH (o_clerk)
     INDEX OrdxB (o_orderkey, o_orderdate);

HELP INDEX generates the following report on this table. The BTEQ commands are included to show how the report format was derived. Attributes of particular interest for this example are highlighted in boldface.

     BTEQ -- Enter your DBC/SQL request or BTEQ command: 
     .sidetitles
     .sidetitles
      BTEQ -- Enter your DBC/SQL request or BTEQ command: 
     .foldline
     .foldline
      BTEQ -- Enter your DBC/SQL request or BTEQ command: 
     HELP INDEX orders;
     HELP INDEX orders;
      *** Help information returned. 13 rows.
      *** Total elapsed time was 1 second.
Unique? N
    Primary//or//Secondary? P
               Column Names o_orderkey
                   Index Id 1
          Approximate Count 0
                 Index Name OrdPI
   Ordered//or//Partitioned? P
              CDT Index Type N
                    Unique? Y
    Primary//or//Secondary? S
               Column Names o_orderkey
                   Index Id 4
          Approximate Count 0
                 Index Name OrdUSI
  Ordered//or//Partitioned? H
              CDT Index Type N
                    Unique? N
    Primary//or//Secondary? S
               Column Names o_orderdate
                   Index Id 8
          Approximate Count 0
                 Index Name Ordx1
  Ordered//or//Partitioned? H
              CDT Index Type N
                    Unique? N
    Primary//or//Secondary? S
               Column Names o_orderdate
                   Index Id 12
          Approximate Count 0
                 Index Name Ordx2
  Ordered//or//Partitioned? V
              CDT Index Type N
                    Unique? N
    Primary//or//Secondary? S
               Column Names o_custkey, o_orderdate
                   Index Id 16
          Approximate Count 0
                 Index Name Ordx3
  Ordered//or//Partitioned? V
              CDT Index Type N
                    Unique? N
    Primary//or//Secondary? S
               Column Names o_custkey, o_orderdate
                   Index Id 24
          Approximate Count 0
                 Index Name Ordx4
  Ordered//or//Partitioned? H
              CDT Index Type N
                    Unique? N
    Primary//or//Secondary? S
               Column Names o_custkey, o_orderstatus
                   Index Id 32
          Approximate Count 0
                 Index Name Ordx5
  Ordered//or//Partitioned? V
              CDT Index Type N
                    Unique? N
    Primary//or//Secondary? S
               Column Names o_custkey, o_orderstatus
                   Index Id 40
          Approximate Count 0
                 Index Name Ordx6
  Ordered//or//Partitioned? H
              CDT Index Type N
                    Unique? N
    Primary//or//Secondary? S
               Column Names o_custkey
                   Index Id 48
          Approximate Count 0
                 Index Name Ordx7
  Ordered//or//Partitioned? V
              CDT Index Type N
                    Unique? N
    Primary//or//Secondary? S
               Column Names o_custkey
                   Index Id 52

Example: HELP INDEX for a Geospatial Index

This example demonstrates a HELP INDEX report on table gt1 that has a NUSI named gt1_geoidx defined on the geospatial column geo that has the ST_Geometry data type.

     CREATE SET TABLE jw.gt1  (
       a   INTEGER,
       geo SYSUDTLIB.ST_Geometry,
       c   INTEGER)
     PRIMARY INDEX (a)
     INDEX gt1_geoidx (geo);

A HELP INDEX request on the NUSI named gt1_geoidx for this table returns the following.

     HELP INDEX gt1_geoidx (geo); 
Unique? N
    Primary//or//Secondary? S
               Column Names geo
                   Index Id 4
          Approximate Count 14
                 Index Name gt1_idx
  Ordered//or//Partitioned? H
             CDT Index Type G
          Approximate Count 0
                 Index Name Ordx8
  Ordered//or//Partitioned? H
              CDT Index Type N
                    Unique? N
    Primary//or//Secondary? S
               Column Names o_shippriority
                   Index Id 56
          Approximate Count 0
                 Index Name Ordx9
  Ordered//or//Partitioned? H
              CDT Index Type N
                    Unique? N
    Primary//or//Secondary? S
               Column Names o_clerk
                   Index Id 60
          Approximate Count 0
                 Index Name OrdxA
  Ordered//or//Partitioned? H
              CDT Index Type N
                    Unique? N
    Primary//or//Secondary? S
               Column Names o_orderkey, o_orderdate
                   Index Id 64
          Approximate Count 0
                 Index Name OrdxB
  Ordered//or//Partitioned? H
              CDT Index Type N
The CDT Index Type attribute is G to signify that the index has a Geospatial data type. If the attribute is N, the index is not a CDT Index Type.