Join Indexes and Tactical Queries
Single-Table Join Indexes
One of the most useful constructs for tactical queries is the single-table join index. Because you can define a primary index for the join index composed of different columns than those used to define the base table primary index, you can create an alternative method of directly accessing data in the associated base table.
For example, you could create a join index on the orders table that includes only a subset of the columns that a particular tactical query application might require. In the example shown in the following graphic, assume that the application has available a value for o_custkey and the clerk that placed the order, but does not have a value for o_orderkey. Specifying the primary index defined for the join index OrderJI supports direct access to order base table using the single‑table join index OrderJI.
CREATE JOIN INDEX OrderJI AS
SELECT o_custkey, o_clerk, o_totalprice, o_orderdate, o_orderkey
FROM orders
PRIMARY INDEX(o_custkey,o_clerk);
A test against an orders table with 75 million rows, both with and without the OrderJI join index, returned the following response times from the query and join index above:
|
If the query is run … |
The response time is … |
|
without the join index |
1:55. |
|
with the join index |
subsecond. |
The larger the base table is, the longer it takes to process via a table scan, and the greater the benefit a join index providing single-AMP access provides. In this example, the join index took 8:23 to create.
A single-table join index like this one is particularly useful when the tactical application does not have the primary index of the base table available, but has an alternative row identifier. This might be the case when a social security number is available, but a member ID, the primary index of the base table, is not. Single-AMP access would still be achievable using the join index if its primary index is defined on social security number.