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.
  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, 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:
    FROM /investors inv, /securities sc, inv.heldSecurities inv_hs
        WHERE sc.status = "active"
        AND = "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 or both.