HELP INDEXの例 - Advanced SQL Engine - Teradata Database

例: テーブル列のHELP INDEX

次の例は、テーブルのcname_1、cname_2、およびcname_3という名前の列に定義されている指定したインデックスについての情報を返します。

HELP INDEX tname (cname_1, cname_2, cname_3);

例: テーブルのHELP INDEX

次のリクエストおよび応答は、Department表にインデックスが1つ定義されていることを示します。

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

例: パーティション プライマリ インデックス

この例では、次の テーブル定義を使用します。

     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は、このテーブルについて次のようなレポートを生成します。報告の形式を設定するために、いくつかのBTEQコマンドを組み込んでいます。また、この例で特に注目するべき属性を太字で示しています。

     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

例: 地理空間インデックスに対するHELP INDEX

この例では、テーブルgt1についてのHELP INDEXレポートを示します。このテーブルには、ST_Geometryデータ型の地理空間列geoに定義されたgt1_geoidxというNUSIがあります。

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

このテーブルのgt1_geoidxという名前のNUSIに対するHELP INDEXリクエストは、次の内容を返します。

     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