15.10 - Example: Partitioned Primary Index Table - 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

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 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 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.

Note the following things about this report.

  • o_orderstatus is a component of a composite secondary index.
  • o_totalprice is not a component of any index.
         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 ?