The primary function of a simple join index is to provide the Optimizer with a high-performing, cost-effective means for satisfying any query that specifies a frequently performed join operation. The simple join index permits you to define a permanent prejoin table without violating the normalization of the database schema. Simple join indexes are also referred to as multitable join indexes.
Simple Join Index Example
For example, suppose that a common task is to look up customer orders by customer number and date. You might create a join index like the following, linking the customer table, the order table and the order detail table:
CREATE JOIN INDEX cust_ord2 AS SELECT cust.customerid,cust.loc,ord.ordid,item,qty,odate FROM cust, ord, orditm WHERE cust.customerid = ord.customerid AND ord.ordid = orditm.ordid;
While you might never issue a query that completely joined these three tables, the key benefit of this join index is its versatility. For example, a query that only looks at the customers for a single state, like the following, can still use the cust_ord2 join index rather than accessing its underlying base tables.
SELECT cust.customerid, ord.ordid, item, qty FROM cust, ord, orditm WHERE cust.customerid = ord.customerid AND ord.ordid = orditm.ordid AND cust.loc = 'WI';
Simple Join Indexes and Partial Query Covering
It is also possible for a join index to be used to partially cover a query to improve query performance. For example, if you wanted to count the number of orders made by customers in the European region during October, you might use the following query:
SELECT cust.customerid,COUNT(ord.ordid) FROM cust, ord, orditm, location WHERE ord.ordid = orditm.ordid AND cust.customerid = ord.customerid AND cust.loc = location.loc AND location.region = 'EUROPE' AND EXTRACT(MONTH, ord.orddate) = 10 GROUP BY cust.customerid;
In this example, the query includes the location table which is not included in the join index. Vantage can still use the join index to partially cover the query by joining the contents of the join index with the location table.
See Partial Query Coverage for more information about partial query coverage.