Suppose you define the following customer and orders tables.
CREATE TABLE customer ( c_custkey INTEGER NOT NULL, c_name CHARACTER(26) NOT NULL, c_address VARCHAR(41), c_nationkey INTEGER, c_phone CHARACTER(16), c_acctbal DECIMAL(13,2), c_mktsegment CHARACTER(21), c_comment VARCHAR(127)) PRIMARY INDEX(c_custkey); CREATE TABLE orders ( o_orderkey INTEGER NOT NULL, o_date DATE FORMAT ‘yyyy-mm-dd’, o_status CHARACTER(1), o_custkey INTEGER, o_totalprice DECIMAL(13,2), o_orderpriority CHARACTER(21), o_clerk CHARACTER(16), o_shippriority INTEGER, o_comment VARCHAR(79)) UNIQUE PRIMARY INDEX(o_orderkey);
Example Query Request
Consider the following SELECT request against these tables.
SELECT o_custkey, c_name, o_status, o_date, o_comment FROM orders, customer WHERE o_custkey=c_custkey;
The next topics examine the query plan for this SELECT request, first without and then with a join index.