例: パーティション プライマリ インデックス テーブル - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 構文規則および例

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
2021年1月
Language
日本語
Last Update
2021-03-30
dita:mapPath
ja-JP/ncd1596241368722.ditamap
dita:ditavalPath
ja-JP/ncd1596241368722.ditaval
dita:id
B035-1144
Product Category
Software
Teradata Vantage

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

     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 orders_PI (o_orderkey)
     PARTITION BY RANGE_N(o_orderdate BETWEEN DATE '1992-01-01'
                                      AND     DATE '1998-12-31' 
                                      EACH INTERVAL '1' MONTH)
     UNIQUE INDEX (o_orderkey)
     INDEX (o_custkey)
     INDEX (o_orderdate) ORDER BY VALUES
     INDEX (o_orderdate)
     INDEX (o_orderdate, o_custkey) ORDER BY VALUES(o_orderdate)
     INDEX (o_orderdate, o_orderstatus);

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

このレポートについては、以下の点に注意が必要です。

  • o_orderstatusは、複合セカンダリ インデックスのメンバーです。
  • o_totalpriceは、どのインデックスのメンバーにもなっていません。
         BTEQ -- Enter your DBC/SQL request or BTEQ command: 
         .SIDETITLES
          BTEQ -- Enter your DBC/SQL request or BTEQ command: 
         .FOLDLINE
          BTEQ -- Enter your DBC/SQL request or BTEQ command:
         HELP COLUMN * FROM orders;
          *** Help information returned. 9 rows.
          *** Total elapsed time was 1 second.
Column Name o_orderkey
Type I
Nullable N
Format -(10)9
Max Length 4
Decimal Total Digits ?
Decimal Fractional Digits ?
Range Low ?
Range High ?
UpperCase N
Table/View? T
Indexed? Y
Unique? N
Primary? P
Title ?
Column Constraint ?
Char Type ?
IdCol Type ?
UDT Name ?
Temporal Column ?
Current ValidTime Unique ?
Sequenced ValidTime Unique ?
NonSequenced ValidTime Unique ?
Current TransactionTime Unique ?
Partitioning Column N
Column Partition Number 0
Column Partition Format NA
Column Partition AC NA
Security Constraint ?
Derived_UDT ?
Derived_UDTFieldID ?
Column Dictionary Name o_orderkey
Column SQL Name o_orderkey
Column Name UEscape ?
Dictionary Title ?
SQL Title ?
Title UEscape ?
UDT Database Dictionary Name ?
UDT Database SQL Name ?
UDT Database Name UEscape ?
UDT Dictionary Name ?
UDT SQL Name ?
UDT Name UEscape ?
Without Overlaps Unique ?
Storage Format ?
Column Name o_custkey
Type I
Nullable Y
Format -(10)9
Max Length 4
Decimal Total Digits ?
Decimal Fractional Digits ?
Range Low ?
Range High ?
UpperCase N
Table/View? T
Indexed? Y
Unique? N
Primary? S
Title ?
Column Constraint ?
Char Type ?
IdCol Type ?
UDT Name ?
Temporal Column N
Current ValidTime Unique ?
Sequenced ValidTime Unique ?
NonSequenced ValidTime Unique ?
Current TransactionTime Unique ?
Partitioning Column N
Column Partition Number 0
Column Partition Format NA
Column Partition AC N
Security Constraint ?
Derived_UDT ?
Derived_UDTFieldID ?
Column Dictionary Name o_custkey
Column SQL Name o_custkey
Column Name UEscape ?
Dictionary Title ?
SQL Title ?
Title UEscape ?
UDT Database Dictionary Name ?
UDT Database SQL Name ?
UDT Database Name UEscape ?
UDT Dictionary Name ?
UDT SQL Name ?
UDT Name UEscape ?
Without Overlaps Unique ?
Storage Format ?
Column Name o_orderstatus
Type CF
Nullable Y
Format X(1)
Max Length 1
Decimal Total Digits ?
Decimal Fractional Digits ?
Range Low ?
Range High ?
UpperCase C
Table/View? T
Indexed? Y
Unique? N
Primary? S
Title ?
Column Constraint ?
Char Type 1
IdCol Type ?
UDT Name ?
Temporal Column N
Current ValidTime Unique ?
Sequenced ValidTime Unique ?
NonSequenced ValidTime Unique ?
Current TransactionTime Unique ?
Partitioning Column N
Column Partition Number 0
Column Partition Format NA
Column Partition AC NA
Security Constraint ?
Derived_UDT ?
Derived_UDTFieldID ?
Column Dictionary Name o_orderstatus
Column SQL Name o_orderstatus
Column Name UEscape ?
Dictionary Title ?
SQL Title ?
Title UEscape ?
UDT Database Dictionary Name ?
UDT Database SQL Name ?
UDT Database Name UEscape ?
UDT Dictionary Name ?
UDT SQL Name ?
UDT Name UEscape ?
Without Overlaps Unique ?
Storage Format ?
Column Name o_totalprice
Type D
Nullable N
Format ------------.99
Max Length 8
Decimal Total Digits 13
Decimal Fractional Digits 2
Range Low ?
Range High ?
UpperCase N
Table/View? T
Indexed? N
Unique? ?
Primary? ?
Title ?
 Column Constraint ?
Char Type ?
IdCol Type ?
UDT Name ?
Temporal Column N
Current ValidTime Unique ?
Sequenced ValidTime Unique ?
 NonSequenced ValidTime Unique ?
Current TransactionTime Unique ?
Partitioning Column N
Column Partition Number 0
Column Partition Format NA
Column Partition AC NA
Security Constraint N
Derived_UDT ?
Derived_UDTFieldID ?
Column Dictionary Name o_totalprice
Column SQL Name o_totalprice
Column Name UEscape ?
Dictionary Title ?
SQL Title ?
Title UEscape ?
UDT Database Dictionary Name ?
UDT Database SQL Name ?
UDT Database Name UEscape ?
UDT Dictionary Name ?
UDT SQL Name ?
UDT Name UEscape ?
Without Overlaps Unique ?
Storage Format ?
Column Name o_orderdate
Type DA
Nullable N
Format yyyy-mm-dd
Max Length 4
Decimal Total Digits ?
Decimal Fractional Digits ?
Range Low ?
Range High ?
UpperCase N
Table/View? T
Indexed? Y
Unique? N
Primary? S
Title ?
Column Constraint ?
Char Type ?
IdCol Type ?
UDT Name ?
Temporal Column N
Current ValidTime Unique ?
Sequenced ValidTime Unique ?
 NonSequenced ValidTime Unique ?
Current TransactionTime Unique ?
Partitioning Column N
Column Partition Number 0
Column Partition Format NA
Column Partition AC NA
Security Constraint ?
Derived_UDT ?
Derived_UDTFieldID ?
Column Dictionary Name o_orderdate
Column SQL Name o_orderdate
Column Name UEscape ?
Dictionary Title ?
SQL Title ?
Title UEscape ?
UDT Database Dictionary Name ?
UDT Database SQL Name ?
UDT Database Name UEscape ?
UDT Dictionary Name ?
UDT SQL Name ?
UDT Name UEscape ?
Without Overlaps Unique ?
Storage Format ?
Column Name o_orderpriority
Type CF
Nullable Y
Format X(21)
Max Length 21
Decimal Total Digits ?
Decimal Fractional Digits ?
Range Low ?
Range High ?
UpperCase N
Table/View? T
Indexed? N
Unique? ?
Primary? ?
Title ?
Column Constraint ?
Char Type 1
IdCol Type ?
UDT Name ?
Temporal Column N
Current ValidTime Unique ?
Sequenced ValidTime Unique ?
 NonSequenced ValidTime Unique ?
Current TransactionTime Unique ?
Partitioning Column N
Column Partition Number 0
Column Partition Format NA
Column Partition AC NA
Security Constraint ?
Derived_UDT ?
Derived_UDTFieldID ?
Column Dictionary Name o_orderpriority
Column SQL Name o_orderpriority
Column Name UEscape ?
Dictionary Title ?
SQL Title ?
Title UEscape ?
UDT Database Dictionary Name ?
UDT Database SQL Name ?
UDT Database Name UEscape ?
UDT Dictionary Name ?
UDT SQL Name ?
UDT Name UEscape ?
Without Overlaps Unique ?
Storage Format ?
Column Name o_clerk
Type CF
Nullable Y
Format X(16)
Max Length 16
Decimal Total Digits ?
Decimal Fractional Digits ?
Range Low ?
Range High ?
UpperCase N
Table/View? T
Indexed? N
Unique? ?
Primary? ?
Title ?
 Column Constraint ?
Char Type 1
IdCol Type ?
UDT Name ?
 Temporal Column N
Current ValidTime Unique ?
Sequenced ValidTime Unique ?
 NonSequenced ValidTime Unique ?
Current TransactionTime Unique ?
Partitioning Column N
Column Partition Number 0
Column Partition Format NA
Column Partition AC NA
Security Constraint ?
Derived_UDT ?
Derived_UDTFieldID ?
Column Dictionary Name o_clerk
Column SQL Name o_clerk
Column Name UEscape ?
Dictionary Title ?
SQL Title ?
Title UEscape ?
UDT Database Dictionary Name ?
UDT Database SQL Name ?
UDT Database Name UEscape ?
UDT Dictionary Name ?
UDT SQL Name ?
UDT Name UEscape ?
Without Overlaps Unique ?
Storage Format ?
Column Name o_shippriority
Type I
Nullable Y
Format -(10)9
Max Length 4
Decimal Total Digits ?
Decimal Fractional Digits ?
Range Low ?
Range High ?
UpperCase N
Table/View? T
Indexed? N
Unique? ?
Primary? ?
Title ?
Column Constraint ?
Char Type ?
IdCol Type ?
UDT Name ?
Temporal Column N
Current ValidTime Unique ?
Sequenced ValidTime Unique ?
NonSequenced ValidTime Unique ?
Current TransactionTime Unique ?
Partitioning Column N
Column Partition Number 0
Column Partition Format NA
Column Partition AC NA
Security Constraint N
Derived_UDT ?
Derived_UDTFieldID ?
Column Dictionary Name o_shippriority
Column SQL Name o_shippriority
Column Name UEscape ?
Dictionary Title ?
SQL Title ?
Title UEscape ?
UDT Database Dictionary Name ?
UDT Database SQL Name ?
UDT Database Name UEscape ?
UDT Dictionary Name ?
UDT SQL Name ?
UDT Name UEscape ?
Without Overlaps Unique ?
Storage Format ?
Column Name o_comment
Type CV
Nullable Y
Format X(79)
Max Length 79
Decimal Total Digits ?
Decimal Fractional Digits ?
Range Low ?
Range High ?
UpperCase N
Table/View? T
Indexed? N
Unique? ?
Primary? ?
Title ?
Column Constraint ?
Char Type 1
IdCol Type ?
UDT Name ?
Temporal Column N
Current ValidTime Unique ?
Sequenced ValidTime Unique ?
NonSequenced ValidTime Unique ?
Current TransactionTime Unique ?
Partitioning Column Y
Column Partition Number 0
Column Partition Format NA
Column Partition AC NA
Security Constraint N
Derived_UDT ?
Derived_UDTFieldID ?
Column Dictionary Name o_comment
Column SQL Name o_comment
Column Name UEscape ?
Dictionary Title ?
SQL Title ?
Title UEscape ?
UDT Database Dictionary Name ?
UDT Database SQL Name ?
UDT Database Name UEscape ?
UDT Dictionary Name ?
UDT SQL Name ?
UDT Name UEscape ?
Without Overlaps Unique ?
Storage Format ?