Create a Vector Table
To run the benchmark, we need to create the lineitem table. The CREATE TABLE statement by default creates a table with VECTORWISE storage.
To create the lineitem table with VECTORWISE storage
1. Connect to the database created in the previous step by issuing the sql command at the operating system prompt, as follows:
sql dbtest
The Ingres Terminal Monitor starts.
2. Type the following SQL statement at the terminal monitor prompt:
CREATE TABLE lineitem (
l_orderkey INTEGER NOT NULL,
l_partkey INTEGER NOT NULL,
l_suppkey INTEGER NOT NULL,
l_linenumber INTEGER NOT NULL,
l_quantity DECIMAL(2,0) NOT NULL,
l_extendedprice DECIMAL(8,2) NOT NULL,
l_discount DECIMAL(2,2) NOT NULL,
l_tax DECIMAL(2,2) NOT NULL,
l_returnflag CHAR(1) NOT NULL,
l_linestatus CHAR(1) NOT NULL,
l_shipdate ANSIDATE NOT NULL,
l_commitdate ANSIDATE NOT NULL,
l_receiptdate ANSIDATE NOT NULL,
l_shipinstruct CHAR(25) NOT NULL,
l_shipmode CHAR(10) NOT NULL,
l_comment VARCHAR(44) NOT NULL)
WITH PARTITION = (HASH ON l_orderkey x PARTITIONS)\g
Notes:
• We recommend declaring key constraints. (We did not do so here for the sake of brevity.) If a column always contains a value then you should define the column as NOT NULL.