diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/algebra/operators/physical/BTreeSearchPOperator.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/algebra/operators/physical/BTreeSearchPOperator.java
index 09a4c6b..f0880ec 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/algebra/operators/physical/BTreeSearchPOperator.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/algebra/operators/physical/BTreeSearchPOperator.java
@@ -45,12 +45,14 @@
 
     private final List<LogicalVariable> lowKeyVarList;
     private final List<LogicalVariable> highKeyVarList;
-    private boolean isPrimaryIndex;
+    private final boolean isPrimaryIndex;
+    private final boolean isEqCondition;
 
     public BTreeSearchPOperator(IDataSourceIndex<String, AqlSourceId> idx, boolean requiresBroadcast,
-            boolean isPrimaryIndex, List<LogicalVariable> lowKeyVarList, List<LogicalVariable> highKeyVarList) {
+            boolean isPrimaryIndex, boolean isEqCondition, List<LogicalVariable> lowKeyVarList, List<LogicalVariable> highKeyVarList) {
         super(idx, requiresBroadcast);
         this.isPrimaryIndex = isPrimaryIndex;
+        this.isEqCondition = isEqCondition;
         this.lowKeyVarList = lowKeyVarList;
         this.highKeyVarList = highKeyVarList;
     }
@@ -100,13 +102,13 @@
     public PhysicalRequirements getRequiredPropertiesForChildren(ILogicalOperator op,
             IPhysicalPropertiesVector reqdByParent) {
         if (requiresBroadcast) {
-            if (isPrimaryIndex) {
-                // For primary indexes, we require re-partitioning on the primary key, and not a broadcast.
-                // Also, add a local sorting property to enforce a sort before the primary-index operator.
+            // For primary indexes optimizing an equality condition we can reduce the broadcast requirement to hash partitioning.
+            if (isPrimaryIndex && isEqCondition) {
                 StructuralPropertiesVector[] pv = new StructuralPropertiesVector[1];
                 ListSet<LogicalVariable> searchKeyVars = new ListSet<LogicalVariable>();
                 searchKeyVars.addAll(lowKeyVarList);
                 searchKeyVars.addAll(highKeyVarList);
+                // Also, add a local sorting property to enforce a sort before the primary-index operator.
                 List<ILocalStructuralProperty> propsLocal = new ArrayList<ILocalStructuralProperty>();
                 for (LogicalVariable orderVar : searchKeyVars) {
                     propsLocal.add(new LocalOrderProperty(new OrderColumn(orderVar, OrderKind.ASC)));
diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/SetAsterixPhysicalOperatorsRule.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/SetAsterixPhysicalOperatorsRule.java
index 19791e3..e19466e 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/SetAsterixPhysicalOperatorsRule.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/SetAsterixPhysicalOperatorsRule.java
@@ -165,8 +165,8 @@
                                 BTreeJobGenParams btreeJobGenParams = new BTreeJobGenParams();
                                 btreeJobGenParams.readFromFuncArgs(f.getArguments());
                                 op.setPhysicalOperator(new BTreeSearchPOperator(dsi, requiresBroadcast,
-                                        btreeJobGenParams.isPrimaryIndex(), btreeJobGenParams.getLowKeyVarList(),
-                                        btreeJobGenParams.getHighKeyVarList()));
+                                        btreeJobGenParams.isPrimaryIndex(), btreeJobGenParams.isEqCondition(),
+                                        btreeJobGenParams.getLowKeyVarList(), btreeJobGenParams.getHighKeyVarList()));
                                 break;
                             }
                             case RTREE: {
diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/BTreeAccessMethod.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/BTreeAccessMethod.java
index 1379bf4..5c65299 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/BTreeAccessMethod.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/BTreeAccessMethod.java
@@ -191,6 +191,7 @@
         // If we can't figure out how to integrate a certain funcExpr into the current predicate, we just bail by setting this flag.
         boolean couldntFigureOut = false;
         boolean doneWithExprs = false;
+        boolean isEqCondition = false;
         // TODO: For now don't consider prefix searches.
         BitSet setLowKeys = new BitSet(numSecondaryKeys);
         BitSet setHighKeys = new BitSet(numSecondaryKeys);
@@ -212,7 +213,7 @@
             }
             ILogicalExpression searchKeyExpr = AccessMethodUtils.createSearchKeyExpr(optFuncExpr, indexSubTree,
                     probeSubTree);
-            LimitType limit = getLimitType(optFuncExpr);
+            LimitType limit = getLimitType(optFuncExpr, probeSubTree);
             switch (limit) {
                 case EQUAL: {
                     if (lowKeyLimits[keyPos] == null && highKeyLimits[keyPos] == null) {
@@ -221,14 +222,23 @@
                         lowKeyExprs[keyPos] = highKeyExprs[keyPos] = searchKeyExpr;
                         setLowKeys.set(keyPos);
                         setHighKeys.set(keyPos);
+                        isEqCondition = true;
                     } else {
+                        // Has already been set to the identical values. When optimizing join we may encounter the same optimizable expression twice
+                        // (once from analyzing each side of the join)
+                        if (lowKeyLimits[keyPos] == limit && lowKeyInclusive[keyPos] == true
+                                && lowKeyExprs[keyPos].equals(searchKeyExpr) && highKeyLimits[keyPos] == limit
+                                && highKeyInclusive[keyPos] == true && highKeyExprs[keyPos].equals(searchKeyExpr)) {
+                            isEqCondition = true;
+                            break;
+                        }
                         couldntFigureOut = true;
                     }
                     // TODO: For now don't consider prefix searches.
                     // If high and low keys are set, we exit for now.
                     if (setLowKeys.cardinality() == numSecondaryKeys && setHighKeys.cardinality() == numSecondaryKeys) {
                         doneWithExprs = true;
-                    }
+                    }                    
                     break;
                 }
                 case HIGH_EXCLUSIVE: {
@@ -237,6 +247,12 @@
                         highKeyExprs[keyPos] = searchKeyExpr;
                         highKeyInclusive[keyPos] = false;
                     } else {
+                        // Has already been set to the identical values. When optimizing join we may encounter the same optimizable expression twice
+                        // (once from analyzing each side of the join)
+                        if (highKeyLimits[keyPos] == limit && highKeyInclusive[keyPos] == false
+                                && highKeyExprs[keyPos].equals(searchKeyExpr)) {
+                            break;
+                        }
                         couldntFigureOut = true;
                         doneWithExprs = true;
                     }
@@ -248,6 +264,12 @@
                         highKeyExprs[keyPos] = searchKeyExpr;
                         highKeyInclusive[keyPos] = true;
                     } else {
+                        // Has already been set to the identical values. When optimizing join we may encounter the same optimizable expression twice
+                        // (once from analyzing each side of the join)
+                        if (highKeyLimits[keyPos] == limit && highKeyInclusive[keyPos] == true
+                                && highKeyExprs[keyPos].equals(searchKeyExpr)) {
+                            break;
+                        }
                         couldntFigureOut = true;
                         doneWithExprs = true;
                     }
@@ -259,6 +281,12 @@
                         lowKeyExprs[keyPos] = searchKeyExpr;
                         lowKeyInclusive[keyPos] = false;
                     } else {
+                        // Has already been set to the identical values. When optimizing join we may encounter the same optimizable expression twice
+                        // (once from analyzing each side of the join)
+                        if (lowKeyLimits[keyPos] == limit && lowKeyInclusive[keyPos] == false
+                                && lowKeyExprs[keyPos].equals(searchKeyExpr)) {
+                            break;
+                        }
                         couldntFigureOut = true;
                         doneWithExprs = true;
                     }
@@ -270,6 +298,12 @@
                         lowKeyExprs[keyPos] = searchKeyExpr;
                         lowKeyInclusive[keyPos] = true;
                     } else {
+                        // Has already been set to the identical values. When optimizing join we may encounter the same optimizable expression twice
+                        // (once from analyzing each side of the join)
+                        if (lowKeyLimits[keyPos] == limit && lowKeyInclusive[keyPos] == true
+                                && lowKeyExprs[keyPos].equals(searchKeyExpr)) {
+                            break;
+                        }
                         couldntFigureOut = true;
                         doneWithExprs = true;
                     }
@@ -328,6 +362,7 @@
                 dataset.getDataverseName(), dataset.getDatasetName(), retainInput, requiresBroadcast);
         jobGenParams.setLowKeyInclusive(lowKeyInclusive[0]);
         jobGenParams.setHighKeyInclusive(highKeyInclusive[0]);
+        jobGenParams.setIsEqCondition(isEqCondition);
         jobGenParams.setLowKeyVarList(keyVarList, 0, numLowKeys);
         jobGenParams.setHighKeyVarList(keyVarList, numLowKeys, numHighKeys);
 
@@ -441,7 +476,7 @@
         return -1;
     }
 
-    private LimitType getLimitType(IOptimizableFuncExpr optFuncExpr) {
+    private LimitType getLimitType(IOptimizableFuncExpr optFuncExpr, OptimizableOperatorSubTree probeSubTree) {
         ComparisonKind ck = AlgebricksBuiltinFunctions.getComparisonType(optFuncExpr.getFuncExpr()
                 .getFunctionIdentifier());
         LimitType limit = null;
@@ -451,19 +486,19 @@
                 break;
             }
             case GE: {
-                limit = constantIsOnLhs(optFuncExpr) ? LimitType.HIGH_INCLUSIVE : LimitType.LOW_INCLUSIVE;
+                limit = probeIsOnLhs(optFuncExpr, probeSubTree) ? LimitType.HIGH_INCLUSIVE : LimitType.LOW_INCLUSIVE;
                 break;
             }
             case GT: {
-                limit = constantIsOnLhs(optFuncExpr) ? LimitType.HIGH_EXCLUSIVE : LimitType.LOW_EXCLUSIVE;
+                limit = probeIsOnLhs(optFuncExpr, probeSubTree) ? LimitType.HIGH_EXCLUSIVE : LimitType.LOW_EXCLUSIVE;
                 break;
             }
             case LE: {
-                limit = constantIsOnLhs(optFuncExpr) ? LimitType.LOW_INCLUSIVE : LimitType.HIGH_INCLUSIVE;
+                limit = probeIsOnLhs(optFuncExpr, probeSubTree) ? LimitType.LOW_INCLUSIVE : LimitType.HIGH_INCLUSIVE;
                 break;
             }
             case LT: {
-                limit = constantIsOnLhs(optFuncExpr) ? LimitType.LOW_EXCLUSIVE : LimitType.HIGH_EXCLUSIVE;
+                limit = probeIsOnLhs(optFuncExpr, probeSubTree) ? LimitType.LOW_EXCLUSIVE : LimitType.HIGH_EXCLUSIVE;
                 break;
             }
             case NEQ: {
@@ -477,11 +512,16 @@
         return limit;
     }
 
-    // Returns true if there is a constant value on the left-hand side  if the given optimizable function (assuming a binary function).
-    public boolean constantIsOnLhs(IOptimizableFuncExpr optFuncExpr) {
-        return optFuncExpr.getFuncExpr().getArguments().get(0) == optFuncExpr.getConstantVal(0);
+    private boolean probeIsOnLhs(IOptimizableFuncExpr optFuncExpr, OptimizableOperatorSubTree probeSubTree) {
+        if (probeSubTree == null) {
+            // We are optimizing a selection query. Search key is a constant. Return true if constant is on lhs.
+            return optFuncExpr.getFuncExpr().getArguments().get(0) == optFuncExpr.getConstantVal(0);
+        } else {
+            // We are optimizing a join query. Determine whether the feeding variable is on the lhs. 
+            return (optFuncExpr.getOperatorSubTree(0) == null || optFuncExpr.getOperatorSubTree(0) == probeSubTree);
+        }
     }
-
+    
     private ILogicalExpression createSelectCondition(List<Mutable<ILogicalExpression>> predList) {
         if (predList.size() > 1) {
             IFunctionInfo finfo = AsterixBuiltinFunctions.getAsterixFunctionInfo(AlgebricksBuiltinFunctions.AND);
diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/BTreeJobGenParams.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/BTreeJobGenParams.java
index 9a735c9..8b7636b 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/BTreeJobGenParams.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/am/BTreeJobGenParams.java
@@ -22,13 +22,14 @@
 
     protected boolean lowKeyInclusive;
     protected boolean highKeyInclusive;
+    protected boolean isEqCondition;
 
     public BTreeJobGenParams() {
         super();
     }
 
-    public BTreeJobGenParams(String indexName, IndexType indexType, String dataverseName, String datasetName, boolean retainInput,
-            boolean requiresBroadcast) {
+    public BTreeJobGenParams(String indexName, IndexType indexType, String dataverseName, String datasetName,
+            boolean retainInput, boolean requiresBroadcast) {
         super(indexName, indexType, dataverseName, datasetName, retainInput, requiresBroadcast);
     }
 
@@ -56,12 +57,17 @@
         this.highKeyInclusive = highKeyInclusive;
     }
 
+    public void setIsEqCondition(boolean isEqConsition) {
+        this.isEqCondition = isEqConsition;
+    }
+
     public void writeToFuncArgs(List<Mutable<ILogicalExpression>> funcArgs) {
         super.writeToFuncArgs(funcArgs);
         writeVarList(lowKeyVarList, funcArgs);
         writeVarList(highKeyVarList, funcArgs);
-        writeKeyInclusive(lowKeyInclusive, funcArgs);
-        writeKeyInclusive(highKeyInclusive, funcArgs);
+        writeBoolean(lowKeyInclusive, funcArgs);
+        writeBoolean(highKeyInclusive, funcArgs);
+        writeBoolean(isEqCondition, funcArgs);
     }
 
     public void readFromFuncArgs(List<Mutable<ILogicalExpression>> funcArgs) {
@@ -71,16 +77,24 @@
         highKeyVarList = new ArrayList<LogicalVariable>();
         int nextIndex = readVarList(funcArgs, index, lowKeyVarList);
         nextIndex = readVarList(funcArgs, nextIndex, highKeyVarList);
-        readKeyInclusives(funcArgs, nextIndex);
+        nextIndex = readKeyInclusives(funcArgs, nextIndex);
+        readIsEqCondition(funcArgs, nextIndex);
     }
 
-    private void readKeyInclusives(List<Mutable<ILogicalExpression>> funcArgs, int index) {
+    private int readKeyInclusives(List<Mutable<ILogicalExpression>> funcArgs, int index) {
         lowKeyInclusive = ((ConstantExpression) funcArgs.get(index).getValue()).getValue().isTrue();
+        // Read the next function argument at index + 1.
         highKeyInclusive = ((ConstantExpression) funcArgs.get(index + 1).getValue()).getValue().isTrue();
+        // We have read two of the function arguments, so the next index is at index + 2.
+        return index + 2;
     }
 
-    private void writeKeyInclusive(boolean keyInclusive, List<Mutable<ILogicalExpression>> funcArgs) {
-        ILogicalExpression keyExpr = keyInclusive ? ConstantExpression.TRUE : ConstantExpression.FALSE;
+    private void readIsEqCondition(List<Mutable<ILogicalExpression>> funcArgs, int index) {
+        isEqCondition = ((ConstantExpression) funcArgs.get(index).getValue()).getValue().isTrue();
+    }
+
+    private void writeBoolean(boolean val, List<Mutable<ILogicalExpression>> funcArgs) {
+        ILogicalExpression keyExpr = val ? ConstantExpression.TRUE : ConstantExpression.FALSE;
         funcArgs.add(new MutableObject<ILogicalExpression>(keyExpr));
     }
 
@@ -92,6 +106,10 @@
         return highKeyVarList;
     }
 
+    public boolean isEqCondition() {
+        return isEqCondition;
+    }
+
     public boolean isLowKeyInclusive() {
         return lowKeyInclusive;
     }
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-neg_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-neg_01.aql
new file mode 100644
index 0000000..75c522b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-neg_01.aql
@@ -0,0 +1,29 @@
+/*
+ * Description  : This is a negative test, mis-spelt/incorrect HINT should result in
+ *                a plan not using an indexed-nested loops join strategy. We expect a hash join. 
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestType as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create dataset test1.DsOne(TestType) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join-neg_01.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key1 /*+ index */ = $y.key2
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-neg_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-neg_02.aql
new file mode 100644
index 0000000..4dd0d00
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-neg_02.aql
@@ -0,0 +1,29 @@
+/*
+ * Description  : This is a negative test, mis-spelt/incorrect HINT should result in
+ *                a plan not using an indexed-nested loops join strategy. We expect a hash join. 
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestType as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create dataset test1.DsOne(TestType) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join-neg_02.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key2 /*+ index */ = $y.key1
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_01.aql
index f7f8d6c..a0877ba 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_01.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_01.aql
@@ -1,46 +1,29 @@
 /*
- * Description    : Equi joins two datasets, Customers and Orders, based on the customer id.
- *                  Given the 'indexnl' hint we expect the join to be transformed
- *                  into an indexed nested-loop join using Customers' primary index.
- * Success        : Yes
+ * Description  : Notice the query hint to use an indexed nested-loops join plan.
+ *              : We expect a plan that hash-exchanges internal dataset DsTwo, then probes internal dataset DsOne’s primary index.
+ * Expected Res : Success
+ * Date         : 29th November 2012
  */
 
-drop dataverse test if exists;
-create dataverse test;
-use dataverse test;
+drop dataverse test1 if exists;
+create dataverse test1;
 
-create type AddressType as closed {
-  number: int32, 
-  street: string,
-  city: string
+create type test1.TestType as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
 }
 
-create type CustomerType as closed {
-  cid: int32, 
-  name: string,
-  age: int32?,
-  address: AddressType?,
-  lastorder: {
-    oid: int32,
-    total: float
-  }
-}
+create dataset test1.DsOne(TestType) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
 
-create type OrderType as closed {
-  oid: int32,
-  cid: int32,
-  orderstatus: string,
-  orderpriority: string,
-  clerk: string,
-  total: float
-}
-
-create dataset Customers(CustomerType) partitioned by key cid;
-create dataset Orders(OrderType) partitioned by key oid;
+// Please note content enclosed in the comment in the predicate is a HINT to the optimizer
 
 write output to nc1:"rttest/btree-index-join_primary-equi-join_01.adm";
 
-for $c in dataset('Customers')
-for $o in dataset('Orders')
-where $c.cid /*+ indexnl */ = $o.cid
-return {"customer":$c, "order": $o} 
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key1 /*+ indexnl */ = $y.key2
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_02.aql
index 9ac6140..59db20b 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_02.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_02.aql
@@ -1,46 +1,29 @@
 /*
- * Description    : Equi joins two datasets, Customers and Orders, based on the customer id.
- *                  Given the 'indexnl' hint we expect the join to be transformed
- *                  into an indexed nested-loop join using Customers' primary index.
- * Success        : Yes
+ * Description  : Notice the query hint to use an indexed nested-loops join plan. 
+ *              : We expect a plan that hash-exchanges internal dataset DsTwo, then probes internal dataset DsOne’s primary index. 
+ * Expected Res : Success
+ * Date         : 29th November 2012
  */
 
-drop dataverse test if exists;
-create dataverse test;
-use dataverse test;
+drop dataverse test1 if exists;
+create dataverse test1;
 
-create type AddressType as closed {
-  number: int32, 
-  street: string,
-  city: string
+create type test1.TestType as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
 }
 
-create type CustomerType as closed {
-  cid: int32, 
-  name: string,
-  age: int32?,
-  address: AddressType?,
-  lastorder: {
-    oid: int32,
-    total: float
-  }
-}
+create dataset test1.DsOne(TestType) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
 
-create type OrderType as closed {
-  oid: int32,
-  cid: int32,
-  orderstatus: string,
-  orderpriority: string,
-  clerk: string,
-  total: float
-}
-
-create dataset Customers(CustomerType) partitioned by key cid;
-create dataset Orders(OrderType) partitioned by key oid;
+// Please note content enclosed in the comment in the predicate is a HINT to the optimizer
 
 write output to nc1:"rttest/btree-index-join_primary-equi-join_02.adm";
 
-for $o in dataset('Orders')
-for $c in dataset('Customers')
-where $o.cid /*+ indexnl */ = $c.cid 
-return {"customer":$c, "order": $o} 
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key2 /*+ indexnl */ = $y.key1
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_03.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_03.aql
index e33e2a9..d60a8ac 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_03.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_03.aql
@@ -1,5 +1,5 @@
 /*
- * Description    : Self-equi joins a dataset, Customers, based on the customer id.
+ * Description    : Equi joins two datasets, Customers and Orders, based on the customer id.
  *                  Given the 'indexnl' hint we expect the join to be transformed
  *                  into an indexed nested-loop join using Customers' primary index.
  * Success        : Yes
@@ -26,11 +26,21 @@
   }
 }
 
+create type OrderType as closed {
+  oid: int32,
+  cid: int32,
+  orderstatus: string,
+  orderpriority: string,
+  clerk: string,
+  total: float
+}
+
 create dataset Customers(CustomerType) partitioned by key cid;
+create dataset Orders(OrderType) partitioned by key oid;
 
-write output to nc1:"rttest/btree-index-join_primary-equi-join_03.adm";
+write output to nc1:"rttest/btree-index-join_primary-equi-join_04.adm";
 
-for $c1 in dataset('Customers')
-for $c2 in dataset('Customers')
-where $c1.cid /*+ indexnl */ = $c2.cid 
-return {"customer1":$c1, "customer2":$c2} 
+for $c in dataset('Customers')
+for $o in dataset('Orders')
+where $c.cid /*+ indexnl */ = $o.cid
+return {"customer":$c, "order": $o} 
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_04.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_04.aql
new file mode 100644
index 0000000..56211e8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_04.aql
@@ -0,0 +1,46 @@
+/*
+ * Description    : Equi joins two datasets, Customers and Orders, based on the customer id.
+ *                  Given the 'indexnl' hint we expect the join to be transformed
+ *                  into an indexed nested-loop join using Customers' primary index.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type AddressType as closed {
+  number: int32, 
+  street: string,
+  city: string
+}
+
+create type CustomerType as closed {
+  cid: int32, 
+  name: string,
+  age: int32?,
+  address: AddressType?,
+  lastorder: {
+    oid: int32,
+    total: float
+  }
+}
+
+create type OrderType as closed {
+  oid: int32,
+  cid: int32,
+  orderstatus: string,
+  orderpriority: string,
+  clerk: string,
+  total: float
+}
+
+create dataset Customers(CustomerType) partitioned by key cid;
+create dataset Orders(OrderType) partitioned by key oid;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join_05.adm";
+
+for $o in dataset('Orders')
+for $c in dataset('Customers')
+where $o.cid /*+ indexnl */ = $c.cid 
+return {"customer":$c, "order": $o} 
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_05.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_05.aql
new file mode 100644
index 0000000..0e6efe8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_05.aql
@@ -0,0 +1,36 @@
+/*
+ * Description    : Self-equi joins a dataset, Customers, based on the customer id.
+ *                  Given the 'indexnl' hint we expect the join to be transformed
+ *                  into an indexed nested-loop join using Customers' primary index.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type AddressType as closed {
+  number: int32, 
+  street: string,
+  city: string
+}
+
+create type CustomerType as closed {
+  cid: int32, 
+  name: string,
+  age: int32?,
+  address: AddressType?,
+  lastorder: {
+    oid: int32,
+    total: float
+  }
+}
+
+create dataset Customers(CustomerType) partitioned by key cid;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join_06.adm";
+
+for $c1 in dataset('Customers')
+for $c2 in dataset('Customers')
+where $c1.cid /*+ indexnl */ = $c2.cid 
+return {"customer1":$c1, "customer2":$c2} 
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_01.aql
new file mode 100644
index 0000000..559ede2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_01.aql
@@ -0,0 +1,29 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan. 
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index. 
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestType as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create dataset test1.DsOne(TestType) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-ge-join_01.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key1 /*+ indexnl */ >= $y.key2
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_02.aql
new file mode 100644
index 0000000..4948b6f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_02.aql
@@ -0,0 +1,29 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan. 
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index. 
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestType as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create dataset test1.DsOne(TestType) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-ge-join_02.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key2 /*+ indexnl */ <= $y.key1
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_01.aql
new file mode 100644
index 0000000..f48cfa3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_01.aql
@@ -0,0 +1,29 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan. 
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index. 
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestType as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create dataset test1.DsOne(TestType) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-gt-join_01.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key1 /*+ indexnl */ > $y.key2
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_02.aql
new file mode 100644
index 0000000..b2f67b3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_02.aql
@@ -0,0 +1,29 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan. 
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index. 
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestType as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create dataset test1.DsOne(TestType) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-gt-join_02.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key2 /*+ indexnl */ < $y.key1
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_01.aql
new file mode 100644
index 0000000..ef4dc05
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_01.aql
@@ -0,0 +1,29 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan. 
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index. 
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestType as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create dataset test1.DsOne(TestType) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-le-join_01.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key1 /*+ indexnl */ <= $y.key2
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_02.aql
new file mode 100644
index 0000000..4c62ebb
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_02.aql
@@ -0,0 +1,29 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan. 
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index. 
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestType as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create dataset test1.DsOne(TestType) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-le-join_02.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key2 /*+ indexnl */ >= $y.key1
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_01.aql
new file mode 100644
index 0000000..79e24cb
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_01.aql
@@ -0,0 +1,29 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan. 
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index. 
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestType as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create dataset test1.DsOne(TestType) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-lt-join_01.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key1 /*+ indexnl */ < $y.key2
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_02.aql
new file mode 100644
index 0000000..9709acd
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_02.aql
@@ -0,0 +1,29 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan. 
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne’s primary index. 
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestType as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create dataset test1.DsOne(TestType) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-lt-join_02.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key2 /*+ indexnl */ > $y.key1
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join-neg_01.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join-neg_01.plan
new file mode 100644
index 0000000..5327887
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join-neg_01.plan
@@ -0,0 +1,17 @@
+-- SINK_WRITE  |PARTITIONED|
+  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- HYBRID_HASH_JOIN [$$7][$$10]  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- DATASOURCE_SCAN  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+          -- HASH_PARTITION_EXCHANGE [$$10]  |PARTITIONED|
+            -- STREAM_PROJECT  |PARTITIONED|
+              -- ASSIGN  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- DATASOURCE_SCAN  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join-neg_02.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join-neg_02.plan
new file mode 100644
index 0000000..9eb8a7f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join-neg_02.plan
@@ -0,0 +1,18 @@
+-- SINK_WRITE  |PARTITIONED|
+  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- HYBRID_HASH_JOIN [$$9][$$8]  |PARTITIONED|
+          -- HASH_PARTITION_EXCHANGE [$$9]  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- DATASOURCE_SCAN  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- STREAM_PROJECT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- DATASOURCE_SCAN  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_01.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_01.plan
index a83e0eb..95a2309 100644
--- a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_01.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_01.plan
@@ -1,16 +1,15 @@
 -- SINK_WRITE  |PARTITIONED|
   -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
-      -- ASSIGN  |PARTITIONED|
-        -- STREAM_PROJECT  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- BTREE_SEARCH  |PARTITIONED|
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-            -- BTREE_SEARCH  |PARTITIONED|
-              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                -- STABLE_SORT [$$11(ASC)]  |PARTITIONED|
-                  -- HASH_PARTITION_EXCHANGE [$$11]  |PARTITIONED|
-                    -- ASSIGN  |PARTITIONED|
-                      -- STREAM_PROJECT  |PARTITIONED|
-                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          -- DATASOURCE_SCAN  |PARTITIONED|
-                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+            -- STABLE_SORT [$$10(ASC)]  |PARTITIONED|
+              -- HASH_PARTITION_EXCHANGE [$$10]  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ASSIGN  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- DATASOURCE_SCAN  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_02.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_02.plan
index d6d4497..b2a3f65 100644
--- a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_02.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_02.plan
@@ -1,16 +1,14 @@
 -- SINK_WRITE  |PARTITIONED|
   -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
-      -- ASSIGN  |PARTITIONED|
-        -- STREAM_PROJECT  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- BTREE_SEARCH  |PARTITIONED|
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-            -- BTREE_SEARCH  |PARTITIONED|
-              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                -- STABLE_SORT [$$10(ASC)]  |PARTITIONED|
-                  -- HASH_PARTITION_EXCHANGE [$$10]  |PARTITIONED|
-                    -- ASSIGN  |PARTITIONED|
-                      -- STREAM_PROJECT  |PARTITIONED|
+            -- STABLE_SORT [$$9(ASC)]  |PARTITIONED|
+              -- HASH_PARTITION_EXCHANGE [$$9]  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- DATASOURCE_SCAN  |PARTITIONED|
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          -- DATASOURCE_SCAN  |PARTITIONED|
-                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_03.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_03.plan
index c5c0f4f..a83e0eb 100644
--- a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_03.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_03.plan
@@ -6,6 +6,11 @@
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
             -- BTREE_SEARCH  |PARTITIONED|
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                -- DATASOURCE_SCAN  |PARTITIONED|
-                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                -- STABLE_SORT [$$11(ASC)]  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$11]  |PARTITIONED|
+                    -- ASSIGN  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- DATASOURCE_SCAN  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_04.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_04.plan
new file mode 100644
index 0000000..d6d4497
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_04.plan
@@ -0,0 +1,16 @@
+-- SINK_WRITE  |PARTITIONED|
+  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- BTREE_SEARCH  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [$$10(ASC)]  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$10]  |PARTITIONED|
+                    -- ASSIGN  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- DATASOURCE_SCAN  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_05.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_05.plan
new file mode 100644
index 0000000..c5c0f4f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-equi-join_05.plan
@@ -0,0 +1,11 @@
+-- SINK_WRITE  |PARTITIONED|
+  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- BTREE_SEARCH  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- DATASOURCE_SCAN  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-ge-join_01.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-ge-join_01.plan
new file mode 100644
index 0000000..6575922
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-ge-join_01.plan
@@ -0,0 +1,13 @@
+-- SINK_WRITE  |PARTITIONED|
+  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- BTREE_SEARCH  |PARTITIONED|
+          -- BROADCAST_EXCHANGE  |PARTITIONED|
+            -- STREAM_PROJECT  |PARTITIONED|
+              -- ASSIGN  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- DATASOURCE_SCAN  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-ge-join_02.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-ge-join_02.plan
new file mode 100644
index 0000000..0abc866
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-ge-join_02.plan
@@ -0,0 +1,12 @@
+-- SINK_WRITE  |PARTITIONED|
+  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- BTREE_SEARCH  |PARTITIONED|
+          -- BROADCAST_EXCHANGE  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- DATASOURCE_SCAN  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-gt-join_01.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-gt-join_01.plan
new file mode 100644
index 0000000..6575922
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-gt-join_01.plan
@@ -0,0 +1,13 @@
+-- SINK_WRITE  |PARTITIONED|
+  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- BTREE_SEARCH  |PARTITIONED|
+          -- BROADCAST_EXCHANGE  |PARTITIONED|
+            -- STREAM_PROJECT  |PARTITIONED|
+              -- ASSIGN  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- DATASOURCE_SCAN  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-gt-join_02.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-gt-join_02.plan
new file mode 100644
index 0000000..0abc866
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-gt-join_02.plan
@@ -0,0 +1,12 @@
+-- SINK_WRITE  |PARTITIONED|
+  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- BTREE_SEARCH  |PARTITIONED|
+          -- BROADCAST_EXCHANGE  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- DATASOURCE_SCAN  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-le-join_01.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-le-join_01.plan
new file mode 100644
index 0000000..6575922
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-le-join_01.plan
@@ -0,0 +1,13 @@
+-- SINK_WRITE  |PARTITIONED|
+  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- BTREE_SEARCH  |PARTITIONED|
+          -- BROADCAST_EXCHANGE  |PARTITIONED|
+            -- STREAM_PROJECT  |PARTITIONED|
+              -- ASSIGN  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- DATASOURCE_SCAN  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-le-join_02.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-le-join_02.plan
new file mode 100644
index 0000000..0abc866
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-le-join_02.plan
@@ -0,0 +1,12 @@
+-- SINK_WRITE  |PARTITIONED|
+  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- BTREE_SEARCH  |PARTITIONED|
+          -- BROADCAST_EXCHANGE  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- DATASOURCE_SCAN  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-lt-join_01.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-lt-join_01.plan
new file mode 100644
index 0000000..6575922
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-lt-join_01.plan
@@ -0,0 +1,13 @@
+-- SINK_WRITE  |PARTITIONED|
+  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- BTREE_SEARCH  |PARTITIONED|
+          -- BROADCAST_EXCHANGE  |PARTITIONED|
+            -- STREAM_PROJECT  |PARTITIONED|
+              -- ASSIGN  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- DATASOURCE_SCAN  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-lt-join_02.plan b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-lt-join_02.plan
new file mode 100644
index 0000000..0abc866
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/btree-index-join/primary-lt-join_02.plan
@@ -0,0 +1,12 @@
+-- SINK_WRITE  |PARTITIONED|
+  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- BTREE_SEARCH  |PARTITIONED|
+          -- BROADCAST_EXCHANGE  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- DATASOURCE_SCAN  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|