Example: Partitioned Primary Index Table - Advanced SQL Engine - Teradata Database

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.