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;Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]