Table Definitions - Teradata Database

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);