Defining a GK Index on a Selection

Suppose a typical query on the sales fact table returns values where state = "CA". To improve the performance for this type of query, you can create a GK index that allows you to store the result of a select statement as a key in an index. The following statement creates the state_idx index, which can improve performance on queries that restrict a search by geographic data:

CREATE GK INDEX state_idx ON geography 
   (SELECT district_code FROM geography 
      WHERE state_code = "CA");

The database server can use the state_idx index on the following type of query that returns revenue and units sold by product, by region, and by month where state = "CA". The database server uses the state_idx index to retrieve rows from the geography table where state = "CA" to improve query performance overall.

SELECT product_name, region, month_name, SUM(revenue),
SUM(units_sold) 
FROM product, geography, time, sales 
WHERE product.product_code = sales.product_code 
   AND geography.district_code = sales.district_code 
   AND state_code = "CA" AND time.time_code = sales.time_code 
GROUP BY product_name, region, month_name 
ORDER BY product_name, region;