SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Compiling and Executing a Query
HP NonStop SQL/MX Query Guide—523728-003
1-13
Factors That Can Affect Compile Time
Example 1-1. OR Optimization DDL
Factors That Can Affect Compile Time
•
The number of tables joined, which is the most important factor in determining
query complexity
CREATE TABLE LINEITEM
( L_ORDERKEY INT NOT NULL
, L_PARTKEY INT NOT NULL
, L_SUPPKEY INT NOT NULL
, L_LINENUMBER INT NOT NULL
, L_QUANTITY NUMERIC(12,2) NOT NULL
, L_EXTENDEDPRICE NUMERIC(12,2) NOT NULL
, L_DISCOUNT NUMERIC(12,2) NOT NULL
, L_TAX NUMERIC(12,2) NOT NULL
, L_RETURNFLAG CHAR(1) NOT NULL
, L_LINESTATUS CHAR(1) NOT NULL
, L_SHIPDATE DATE NOT NULL
, L_COMMITDATE DATE NOT NULL
, L_RECEIPTDATE DATE NOT NULL
, L_SHIPINSTRUCT CHAR(25) NOT NULL
, L_SHIPMODE CHAR(10) NOT NULL
, L_COMMENT VARCHAR(44) NOT NULL
, PRIMARY KEY (L_ORDERKEY, L_LINENUMBER))
STORE BY PRIMARY KEY
PARTITION (
ADD FIRST KEY (100001)
LOCATION $DATA08
NAME PARTN1
EXTENT (1024, 1024) MAXEXTENTS 512
,ADD FIRST KEY (200001)
LOCATION $DATA07
NAME PARTN2
EXTENT (1024, 1024) MAXEXTENTS 512
,ADD FIRST KEY (300001)
LOCATION $DATA06
NAME PARTN3
EXTENT (1024, 1024) MAXEXTENTS 512
);
CREATE INDEX LX1 ON LINEITEM
(L_PARTKEY)
LOCATION $DATA09
ATTRIBUTE EXTENT (1024, 1024) MAXEXTENTS 512
PARTITION
( ADD FIRST KEY (100001)
LOCATION $DATA08
,ADD FIRST KEY (200001)
LOCATION $DATA07
,ADD FIRST KEY (300001)
LOCATION $DATA06
);
CREATE INDEX LX2 ON LINEITEM
(L_SUPPKEY)
LOCATION $DATA09
ATTRIBUTE EXTENT (1024, 1024) MAXEXTENTS 512
PARTITION
( ADD FIRST KEY (2500)
LOCATION $DATA08
,ADD FIRST KEY (5000)
LOCATION $DATA07
,ADD FIRST KEY (7500)
LOCATION $DATA06
);