
-- Column-wise extra indices and single column projections

CREATE 
      INDEX  l_pk 
         ON  lineitem (l_partkey) 
  PARTITION  (l_partkey  INT (0hexffff00))
  ;
CREATE 
      INDEX  l_pksk 
         ON  lineitem (l_partkey, l_suppkey) 
  PARTITION  (l_partkey  INT (0hexffff00))
  ;

CREATE 
  TABLE  l_partkey 
         ( lpk_orderkey    BIGINT, 
           lpk_linenumber  INT, 
           lpk_partkey     BIGINT, 
           PRIMARY KEY (lpk_orderkey, lpk_linenumber)
         );
CREATE 
      INDEX  lpk_pk 
         ON  l_partkey (lpk_partkey)
  PARTITION  (lpk_partkey INT (0hexffff00))
  ;

CREATE 
  TABLE  l_extendedprice 
         ( lep_orderkey       BIGINT,
           lep_linenumber     INT,
           lep_extendedprice  DOUBLE PRECISION NOT NULL,
           PRIMARY KEY (lep_orderkey, lep_linenumber)
         );
CREATE
  TABLE  l_suppkey 
         ( lsk_orderkey    BIGINT, 
           lsk_linenumber  INT, 
           lsk_suppkey     BIGINT NOT NULL, 
           PRIMARY KEY (lsk_orderkey, lsk_linenumber)
         );
CREATE
  TABLE  l_discount 
         ( ld_orderkey    BIGINT, 
           ld_linenumber  INT, 
           ld_discount    DOUBLE PRECISION NOT NULL, 
           PRIMARY KEY (ld_orderkey, ld_linenumber)
         );
CREATE
  TABLE  l_quantity 
         ( lq_orderkey    BIGINT, 
           lq_linenumber  INT, 
           lq_quantity    INT NOT NULL, 
           PRIMARY KEY (lq_orderkey, lq_linenumber)
         );

CREATE
  TABLE  o_orderdate 
         ( od_orderkey   BIGINT PRIMARY KEY, 
           od_orderdate  DATE NOT NULL
         );

CREATE
  TABLE ps_supplycost 
        ( psc_partkey     BIGINT, 
          psc_suppkey     BIGINT, 
          psc_supplycost  DOUBLE PRECISION, 
          PRIMARY KEY (psc_partkey, psc_suppkey)
        );

CREATE
  TABLE s_nationkey 
        ( snk_suppkey    BIGINT PRIMARY KEY, 
          snk_nationkey  INT
        );

CREATE
  TABLE p_name 
        ( pn_partkey  BIGINT PRIMARY KEY, 
          pn_name     VARCHAR
        );


-- Row-wise extra indices and single column projections

__dbf_set ('enable_col_by_default', 0);

USE R;

CREATE 
      INDEX  r_l_pk 
         ON  r..lineitem (l_partkey) 
  PARTITION  (l_partkey  INT (0hexffff00))
  ;

CREATE
      INDEX  r_l_pksk
         ON  r..lineitem (l_partkey, l_suppkey) 
  PARTITION (l_partkey  INT (0hexffff00))
  ;

CREATE
  TABLE  l_partkey
         ( lpk_orderkey    BIGINT,
           lpk_linenumber  INT,
           lpk_partkey     BIGINT,
           PRIMARY KEY (lpk_orderkey, lpk_linenumber)
         );
CREATE 
      INDEX r_lpk_pk 
         ON l_partkey (lpk_partkey) 
  PARTITION (lpk_partkey INT (0hexffff00))
  ;

CREATE
  TABLE  l_extendedprice 
         ( lep_orderkey       BIGINT,
           lep_linenumber     INT,
           lep_extendedprice  DOUBLE PRECISION NOT NULL, 
           PRIMARY KEY (lep_orderkey, lep_linenumber)
         );
CREATE
  TABLE  l_suppkey 
         ( lsk_orderkey    BIGINT, 
           lsk_linenumber  INT, 
           lsk_suppkey     BIGINT NOT NULL, 
           PRIMARY KEY (lsk_orderkey, lsk_linenumber)
         );
CREATE
  TABLE  l_discount 
         ( ld_orderkey    BIGINT, 
           ld_linenumber  INT,
           ld_discount    DOUBLE PRECISION NOT NULL, 
           PRIMARY KEY (ld_orderkey, ld_linenumber)
         );
CREATE
  TABLE  l_quantity 
         ( lq_orderkey    BIGINT, 
           lq_linenumber  INT, 
           lq_quantity    INT NOT NULL, 
           PRIMARY KEY (lq_orderkey, lq_linenumber)
         );

CREATE
  TABLE  o_orderdate 
         ( od_orderkey   BIGINT PRIMARY KEY, 
           od_orderdate  DATE NOT NULL
         );
CREATE
  TABLE  o_custkey 
         ( ock_orderkey  BIGINT PRIMARY KEY, 
           ock_custkey   INT NOT NULL
         );

CREATE
  TABLE  ps_supplycost 
         ( psc_partkey     BIGINT, 
           psc_suppkey     BIGINT, 
           psc_supplycost  DOUBLE PRECISION, 
           PRIMARY KEY (psc_partkey, psc_suppkey)
         );

CREATE 
  TABLE  s_nationkey 
         ( snk_suppkey    BIGINT PRIMARY KEY, 
           snk_nationkey  INT
         );

CREATE 
  TABLE  p_name 
         ( pn_partkey  BIGINT PRIMARY KEY, 
           pn_name     VARCHAR
         );
