Defining a GK Index on Joined Tables

You can create a GK index that allows you to store the result of an intersect of data sets from joined tables as a key in an index. Suppose you want to create a GK index on year data from the time dimension table for each entry in the sales table. The following statement creates the time_idx index:

CREATE GK INDEX time_idx ON sales 
(SELECT year FROM sales, time 
   WHERE sales.time_code = time.time_code);

The database server can use the time_idx index on the following type of query that returns the names of customers who purchased products after 1996:

SELECT customer_name
FROM   sales, customer, time
WHERE  sales.time_code = time.time_code AND year > 1996 
    AND sale.customer_code = customer.customer_code;