15.00 - Table Definitions - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Table Definitions

Suppose you have the following tables that you query frequently using join expressions, and both are very large.

 

Table Name

Primary Index

Primary Index Type

LineItem

OrderKey

NUPI

Part

PartKey

UPI

The table definitions are as follows.

     CREATE TABLE LineItem (
       l_OrderKey      INTEGER NOT NULL,
       l_PartKey       INTEGER NOT NULL,
       1_SupplierKey   INTEGER,
       l_LineNumber    INTEGER,
       l_Quantity      INTEGER NOT NULL,
       l_ExtendedPrice DECIMAL(13,2) NOT NULL,
       l_Discount      DECIMAL(13,2),
       l_Tax           DECIMAL(13,2),
       l_ReturnFlag    CHARACTER(1),
       l_LineStatus    CHARACTER(1),
       l_ShipDate      DATE FORMAT ‘yyyy-mm-dd’,
       l_CommitDate    DATE FORMAT ‘yyyy-mm-dd’,
       l_ReceiptDate   DATE FORMAT ‘yyyy-mm-dd’,
       l_ShipInstruct  VARCHAR(25),
       l_ShipMode      VARCHAR(10),
       l_Comment       VARCHAR(44))
     PRIMARY INDEX (l_OrderKey);
 
     CREATE TABLE part (
        p_PartKey         INTEGER NOT NULL,
        p_PartDescription CHARACTER(26),
        p_SupplierNumber  INTEGER)
     UNIQUE PRIMARY INDEX (p_PartKey);