Using Indexes with Equi-Join Queries
Equi-join queries are queries in which two regions are joined through an equality condition in the WHERE clause.
Create an index for each side of the equi-join condition. The query engine can quickly evaluate the query’s equi-join condition by iterating over the keys of the left-side and right-side indexes for an equality match.
Note: Equi-join queries require regular indexes. Key indexes are not applied to equi-join queries.
For this query:
SELECT DISTINCT inv.name, ord.orderID, ord.status FROM /investors inv, /orders ord WHERE inv.investorID = ord.investorID
Create two indexes:
FROM clause Indexed expression /investors inv inv.investorID /orders ord ord.investorID
If there are additional, single-region queries in a query with an equi-join condition, create additional indexes for the single-region conditions only if you are able to create at least one such index for each region in the query. Any indexing on a subset of the regions in the query will degrade performance.
For this example query:
SELECT DISTINCT * FROM /investors inv, /securities sc, inv.heldSecurities inv_hs WHERE sc.status = "active" AND inv.name = "xyz" AND inv.age > 75 AND inv_hs.secName = sc.secName
Create the indexes for the equi-join condition:
FROM clause Indexed expression /investors inv, inv.heldSecurities inv_hs inv_hs.secName /securities sc sc.secName
Then, if you create any more indexes, create one on
sc.statusand one on