HELP INDEX Examples | Teradata Vantage - 17.10 - HELP INDEX Examples - 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)

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.