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.

To use an index with an equi-join query:

1. 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

2. 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.status and one on inv.age or inv.name or both.