diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/DisjunctivePredicateToJoinRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/DisjunctivePredicateToJoinRule.java
index acbcb7d..17c5e52 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/DisjunctivePredicateToJoinRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/DisjunctivePredicateToJoinRule.java
@@ -23,7 +23,7 @@
 import java.util.HashSet;
 import java.util.List;
 
-import org.apache.asterix.common.annotations.SkipSecondaryIndexSearchExpressionAnnotation;
+import org.apache.asterix.common.annotations.IndexedNLJoinExpressionAnnotation;
 import org.apache.asterix.metadata.declared.MetadataProvider;
 import org.apache.asterix.om.base.AOrderedList;
 import org.apache.asterix.om.constants.AsterixConstantValue;
@@ -43,7 +43,6 @@
 import org.apache.hyracks.algebricks.core.algebra.expressions.BroadcastExpressionAnnotation;
 import org.apache.hyracks.algebricks.core.algebra.expressions.ConstantExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.IExpressionAnnotation;
-import org.apache.hyracks.algebricks.core.algebra.expressions.IndexedNLJoinExpressionAnnotation;
 import org.apache.hyracks.algebricks.core.algebra.expressions.ScalarFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.UnnestingFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression;
@@ -170,9 +169,9 @@
         scanVarRef.setSourceLocation(sourceLoc);
         eqExp.getArguments().add(new MutableObject<>(scanVarRef));
         eqExp.getArguments().add(new MutableObject<>(varEx.cloneExpression()));
-        if (!allAnnotations.contains(SkipSecondaryIndexSearchExpressionAnnotation.INSTANCE)) {
-            eqExp.putAnnotation(IndexedNLJoinExpressionAnnotation.INSTANCE);
-        }
+        // if allAnnotations contains SkipSecondaryIndexSearchExpressionAnnotation then it'll take precedence over
+        // IndexedNLJoinExpressionAnnotation and index will be skipped anyway
+        eqExp.putAnnotation(IndexedNLJoinExpressionAnnotation.INSTANCE_ANY_INDEX);
         BroadcastExpressionAnnotation bcast =
                 new BroadcastExpressionAnnotation(BroadcastExpressionAnnotation.BroadcastSide.LEFT); // Broadcast the OR predicates branch.
         eqExp.putAnnotation(bcast);
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
index 6b045ea..b2fdb74 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
@@ -20,6 +20,7 @@
 
 import java.util.ArrayList;
 import java.util.Arrays;
+import java.util.Collection;
 import java.util.Collections;
 import java.util.HashMap;
 import java.util.HashSet;
@@ -274,8 +275,7 @@
             IOptimizationContext context, IVariableTypeEnvironment typeEnvironment) throws AlgebricksException {
         Iterator<Map.Entry<Index, List<Pair<Integer, Integer>>>> indexExprAndVarIt =
                 analysisCtx.getIteratorForIndexExprsAndVars();
-        // Used to keep track of matched expressions (added for prefix search)
-        int numMatchedKeys = 0;
+        boolean hasIndexPreferences = false;
         ArrayList<Integer> matchedExpressions = new ArrayList<>();
         while (indexExprAndVarIt.hasNext()) {
             Map.Entry<Index, List<Pair<Integer, Integer>>> indexExprAndVarEntry = indexExprAndVarIt.next();
@@ -284,7 +284,8 @@
             boolean allUsed = true;
             int lastFieldMatched = -1;
             matchedExpressions.clear();
-            numMatchedKeys = 0;
+            // Used to keep track of matched expressions (added for prefix search)
+            int numMatchedKeys = 0;
 
             for (int i = 0; i < index.getKeyFieldNames().size(); i++) {
                 List<String> keyField = index.getKeyFieldNames().get(i);
@@ -385,6 +386,8 @@
                             && optFuncExpr.getOperatorSubTree(exprAndVarIdx.second).hasDataSourceScan()) {
                         foundKeyField = true;
                         matchedExpressions.add(exprAndVarIdx.first);
+                        hasIndexPreferences =
+                                hasIndexPreferences || accessMethod.getSecondaryIndexPreferences(optFuncExpr) != null;
                     }
                 }
                 if (foundKeyField) {
@@ -419,7 +422,15 @@
                     continue;
                 }
             }
-            analysisCtx.putNumberOfMatchedKeys(index, Integer.valueOf(numMatchedKeys));
+            analysisCtx.putNumberOfMatchedKeys(index, numMatchedKeys);
+        }
+
+        if (hasIndexPreferences) {
+            Collection<Index> preferredSecondaryIndexes = fetchSecondaryIndexPreferences(accessMethod, analysisCtx);
+            if (preferredSecondaryIndexes != null) {
+                // if we have preferred indexes then remove all non-preferred indexes
+                removeNonPreferredSecondaryIndexes(analysisCtx, preferredSecondaryIndexes);
+            }
         }
     }
 
@@ -436,6 +447,42 @@
                 Index.getNonNullableType(type2).first.getTypeTag());
     }
 
+    private Set<Index> fetchSecondaryIndexPreferences(IAccessMethod accessMethod,
+            AccessMethodAnalysisContext analysisCtx) {
+        Set<Index> preferredSecondaryIndexes = null;
+        for (Iterator<Map.Entry<Index, List<Pair<Integer, Integer>>>> indexExprAndVarIt =
+                analysisCtx.getIteratorForIndexExprsAndVars(); indexExprAndVarIt.hasNext();) {
+            Map.Entry<Index, List<Pair<Integer, Integer>>> indexExprAndVarEntry = indexExprAndVarIt.next();
+            Index index = indexExprAndVarEntry.getKey();
+            if (index.isSecondaryIndex()) {
+                for (Pair<Integer, Integer> exprVarPair : indexExprAndVarEntry.getValue()) {
+                    IOptimizableFuncExpr optFuncExpr = analysisCtx.getMatchedFuncExpr(exprVarPair.first);
+                    Collection<String> preferredIndexNames = accessMethod.getSecondaryIndexPreferences(optFuncExpr);
+                    if (preferredIndexNames != null && preferredIndexNames.contains(index.getIndexName())) {
+                        if (preferredSecondaryIndexes == null) {
+                            preferredSecondaryIndexes = new HashSet<>();
+                        }
+                        preferredSecondaryIndexes.add(index);
+                        break;
+                    }
+                }
+            }
+        }
+        return preferredSecondaryIndexes;
+    }
+
+    private void removeNonPreferredSecondaryIndexes(AccessMethodAnalysisContext analysisCtx,
+            Collection<Index> preferredIndexes) {
+        for (Iterator<Map.Entry<Index, List<Pair<Integer, Integer>>>> indexExprAndVarIt =
+                analysisCtx.getIteratorForIndexExprsAndVars(); indexExprAndVarIt.hasNext();) {
+            Map.Entry<Index, List<Pair<Integer, Integer>>> indexExprAndVarEntry = indexExprAndVarIt.next();
+            Index index = indexExprAndVarEntry.getKey();
+            if (index.isSecondaryIndex() && !preferredIndexes.contains(index)) {
+                indexExprAndVarIt.remove();
+            }
+        }
+    }
+
     /**
      * Analyzes the given selection condition, filling analyzedAMs with
      * applicable access method types. At this point we are not yet consulting
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java
index dc83fb1..e003ea5 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java
@@ -20,6 +20,7 @@
 package org.apache.asterix.optimizer.rules.am;
 
 import java.util.ArrayList;
+import java.util.Collection;
 import java.util.Collections;
 import java.util.HashSet;
 import java.util.Iterator;
@@ -28,6 +29,8 @@
 import java.util.Set;
 
 import org.apache.asterix.algebra.operators.physical.ExternalDataLookupPOperator;
+import org.apache.asterix.common.annotations.AbstractExpressionAnnotationWithIndexNames;
+import org.apache.asterix.common.annotations.SkipSecondaryIndexSearchExpressionAnnotation;
 import org.apache.asterix.common.config.DatasetConfig.DatasetType;
 import org.apache.asterix.common.config.DatasetConfig.IndexType;
 import org.apache.asterix.common.exceptions.CompilationException;
@@ -2659,4 +2662,16 @@
         }
         return null;
     }
+
+    static boolean skipSecondaryIndexRequestedByAnnotation(Index index, IOptimizableFuncExpr optFuncExpr) {
+        SkipSecondaryIndexSearchExpressionAnnotation ann =
+                optFuncExpr.getFuncExpr().getAnnotation(SkipSecondaryIndexSearchExpressionAnnotation.class);
+        return ann != null && (ann.getIndexNames() == null || ann.getIndexNames().contains(index.getIndexName()));
+    }
+
+    static Collection<String> getSecondaryIndexPreferences(IOptimizableFuncExpr optFuncExpr,
+            Class<? extends AbstractExpressionAnnotationWithIndexNames> annClass) {
+        AbstractExpressionAnnotationWithIndexNames ann = optFuncExpr.getFuncExpr().getAnnotation(annClass);
+        return ann == null ? null : ann.getIndexNames();
+    }
 }
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java
index a8f6549..21892cc 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java
@@ -22,13 +22,16 @@
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.BitSet;
+import java.util.Collection;
 import java.util.Collections;
 import java.util.HashSet;
 import java.util.Iterator;
 import java.util.List;
 import java.util.Set;
 
-import org.apache.asterix.common.annotations.SkipSecondaryIndexSearchExpressionAnnotation;
+import org.apache.asterix.common.annotations.AbstractExpressionAnnotationWithIndexNames;
+import org.apache.asterix.common.annotations.IndexedNLJoinExpressionAnnotation;
+import org.apache.asterix.common.annotations.SecondaryIndexSearchPreferenceAnnotation;
 import org.apache.asterix.common.config.DatasetConfig.DatasetType;
 import org.apache.asterix.common.config.DatasetConfig.IndexType;
 import org.apache.asterix.common.exceptions.CompilationException;
@@ -56,7 +59,6 @@
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
 import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.IVariableTypeEnvironment;
-import org.apache.hyracks.algebricks.core.algebra.expressions.IndexedNLJoinExpressionAnnotation;
 import org.apache.hyracks.algebricks.core.algebra.expressions.ScalarFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.UnnestingFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression;
@@ -982,8 +984,7 @@
                 return false;
             }
         }
-        if (!index.isPrimaryIndex()
-                && optFuncExpr.getFuncExpr().hasAnnotation(SkipSecondaryIndexSearchExpressionAnnotation.class)) {
+        if (!index.isPrimaryIndex() && AccessMethodUtils.skipSecondaryIndexRequestedByAnnotation(index, optFuncExpr)) {
             return false;
         }
         // No additional analysis required for BTrees.
@@ -991,6 +992,25 @@
     }
 
     @Override
+    public Collection<String> getSecondaryIndexPreferences(IOptimizableFuncExpr optFuncExpr) {
+        // If we are optimizing a join, check for the indexed nested-loop join hint.
+        Class<? extends AbstractExpressionAnnotationWithIndexNames> annotationClass;
+        if (optFuncExpr.getNumLogicalVars() == 2) {
+            if (optFuncExpr.getOperatorSubTree(0) == optFuncExpr.getOperatorSubTree(1)) {
+                // We are in the select case
+                annotationClass = SecondaryIndexSearchPreferenceAnnotation.class;
+            } else {
+                // We are in the join case
+                annotationClass = IndexedNLJoinExpressionAnnotation.class;
+            }
+        } else {
+            // We are in the select case
+            annotationClass = SecondaryIndexSearchPreferenceAnnotation.class;
+        }
+        return AccessMethodUtils.getSecondaryIndexPreferences(optFuncExpr, annotationClass);
+    }
+
+    @Override
     public String getName() {
         return "BTREE_ACCESS_METHOD";
     }
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IAccessMethod.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IAccessMethod.java
index 8cd0de4..84ee41e 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IAccessMethod.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IAccessMethod.java
@@ -18,6 +18,7 @@
  */
 package org.apache.asterix.optimizer.rules.am;
 
+import java.util.Collection;
 import java.util.List;
 
 import org.apache.asterix.metadata.entities.Index;
@@ -116,6 +117,8 @@
      */
     public boolean exprIsOptimizable(Index index, IOptimizableFuncExpr optFuncExpr) throws AlgebricksException;
 
+    public Collection<String> getSecondaryIndexPreferences(IOptimizableFuncExpr optFuncExpr);
+
     public String getName();
 
 }
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/InvertedIndexAccessMethod.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/InvertedIndexAccessMethod.java
index 1c151a1..10fdfb9 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/InvertedIndexAccessMethod.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/InvertedIndexAccessMethod.java
@@ -20,6 +20,7 @@
 
 import java.util.ArrayList;
 import java.util.Arrays;
+import java.util.Collection;
 import java.util.Collections;
 import java.util.HashMap;
 import java.util.Iterator;
@@ -27,7 +28,7 @@
 import java.util.List;
 import java.util.Map;
 
-import org.apache.asterix.common.annotations.SkipSecondaryIndexSearchExpressionAnnotation;
+import org.apache.asterix.common.annotations.SecondaryIndexSearchPreferenceAnnotation;
 import org.apache.asterix.common.config.DatasetConfig.IndexType;
 import org.apache.asterix.common.exceptions.CompilationException;
 import org.apache.asterix.common.exceptions.ErrorCode;
@@ -960,7 +961,7 @@
 
     @Override
     public boolean exprIsOptimizable(Index index, IOptimizableFuncExpr optFuncExpr) throws AlgebricksException {
-        if (optFuncExpr.getFuncExpr().hasAnnotation(SkipSecondaryIndexSearchExpressionAnnotation.class)) {
+        if (AccessMethodUtils.skipSecondaryIndexRequestedByAnnotation(index, optFuncExpr)) {
             return false;
         }
 
@@ -1333,6 +1334,12 @@
     }
 
     @Override
+    public Collection<String> getSecondaryIndexPreferences(IOptimizableFuncExpr optFuncExpr) {
+        return AccessMethodUtils.getSecondaryIndexPreferences(optFuncExpr,
+                SecondaryIndexSearchPreferenceAnnotation.class);
+    }
+
+    @Override
     public String getName() {
         return "INVERTED_INDEX_ACCESS_METHOD";
     }
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/RTreeAccessMethod.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/RTreeAccessMethod.java
index a578d2c..c1ae61e 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/RTreeAccessMethod.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/RTreeAccessMethod.java
@@ -20,10 +20,11 @@
 
 import java.util.ArrayList;
 import java.util.Arrays;
+import java.util.Collection;
 import java.util.Collections;
 import java.util.List;
 
-import org.apache.asterix.common.annotations.SkipSecondaryIndexSearchExpressionAnnotation;
+import org.apache.asterix.common.annotations.SecondaryIndexSearchPreferenceAnnotation;
 import org.apache.asterix.common.config.DatasetConfig.DatasetType;
 import org.apache.asterix.common.config.DatasetConfig.IndexType;
 import org.apache.asterix.lang.common.util.FunctionUtil;
@@ -370,7 +371,7 @@
 
     @Override
     public boolean exprIsOptimizable(Index index, IOptimizableFuncExpr optFuncExpr) {
-        if (optFuncExpr.getFuncExpr().hasAnnotation(SkipSecondaryIndexSearchExpressionAnnotation.class)) {
+        if (AccessMethodUtils.skipSecondaryIndexRequestedByAnnotation(index, optFuncExpr)) {
             return false;
         }
         // No additional analysis required.
@@ -378,6 +379,12 @@
     }
 
     @Override
+    public Collection<String> getSecondaryIndexPreferences(IOptimizableFuncExpr optFuncExpr) {
+        return AccessMethodUtils.getSecondaryIndexPreferences(optFuncExpr,
+                SecondaryIndexSearchPreferenceAnnotation.class);
+    }
+
+    @Override
     public String getName() {
         return "RTREE_ACCESS_METHOD";
     }
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-3.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-3.sqlpp
new file mode 100644
index 0000000..4fc66fc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-3.sqlpp
@@ -0,0 +1,62 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk1(tenkType) primary key unique2;
+
+create dataset tenk2(tenkType) primary key unique2;
+
+create index idx_tenk2_1k on tenk2(thousand);
+
+create index idx_tenk2_1k_2k on tenk2(thousand, twothousand);
+
+/*
+ * indexnl hint. use specified index (idx_tenk2_1k_2k)
+ */
+
+select value tenk2.unique1
+from tenk1, tenk2
+where
+  tenk1.unique2 < 1
+and
+  tenk1.thousand /* +indexnl(idx_tenk2_1k_2k) */ = tenk2.thousand
+order by tenk2.unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-4.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-4.sqlpp
new file mode 100644
index 0000000..b50da34
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-4.sqlpp
@@ -0,0 +1,65 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk1(tenkType) primary key unique2;
+
+create index idx_tenk1_1k on tenk1(thousand);
+
+create dataset tenk2(tenkType) primary key unique2;
+
+create index idx_tenk2_1k on tenk2(thousand);
+
+create index idx_tenk2_1k_2k on tenk2(thousand, twothousand);
+
+/*
+ * indexnl hint. ignore unknown index
+ * explain plan -> idx_tenk2_1k is used
+ */
+
+select value tenk2.unique1
+from tenk1, tenk2
+where
+  tenk1.unique2 < 1
+and
+  tenk1.thousand /* +indexnl(IDX_UNKNOWN) */ = tenk2.thousand
+order by tenk2.unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-5.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-5.sqlpp
new file mode 100644
index 0000000..cab4e3a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-5.sqlpp
@@ -0,0 +1,65 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk1(tenkType) primary key unique2;
+
+create index idx_tenk1_1k on tenk1(thousand);
+
+create dataset tenk2(tenkType) primary key unique2;
+
+create index idx_tenk2_1k on tenk2(thousand);
+
+create index idx_tenk2_1k_2k on tenk2(thousand, twothousand);
+
+/*
+ * indexnl hint. ignore unknown indexes, use specified index
+ * explain plan -> idx_tenk2_1k_2k is used
+ */
+
+select value tenk2.unique1
+from tenk1, tenk2
+where
+  tenk1.unique2 < 1
+and
+  tenk1.thousand /* +indexnl(IDX_UNKNOWN_1, idx_tenk2_1k_2k, IDX_UNKNOWN_2) */ = tenk2.thousand
+order by tenk2.unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-6.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-6.sqlpp
new file mode 100644
index 0000000..c01019b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-6.sqlpp
@@ -0,0 +1,65 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk1(tenkType) primary key unique2;
+
+create index idx_tenk1_1k on tenk1(thousand);
+
+create dataset tenk2(tenkType) primary key unique2;
+
+create index idx_tenk2_1k on tenk2(thousand);
+
+create index idx_tenk2_1k_2k on tenk2(thousand, twothousand);
+
+/*
+ * indexnl hint. ignore inapplicable index
+ * explain plan -> idx_tenk2_1k is used
+ */
+
+select value tenk2.unique1
+from tenk1, tenk2
+where
+  tenk1.unique2 < 1
+and
+  tenk1.thousand /* +indexnl(idx_tenk1_1k) */ = tenk2.thousand
+order by tenk2.unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-7.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-7.sqlpp
new file mode 100644
index 0000000..bccd58c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/hints-indexnl-params/hints-indexnl-params-7.sqlpp
@@ -0,0 +1,65 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk1(tenkType) primary key unique2;
+
+create index idx_tenk1_1k on tenk1(thousand);
+
+create dataset tenk2(tenkType) primary key unique2;
+
+create index idx_tenk2_1k on tenk2(thousand);
+
+create index idx_tenk2_1k_2k on tenk2(thousand, twothousand);
+
+/*
+ * indexnl hint. ignore inapplicable index, use specified index
+ * explain plan -> idx_tenk2_1k_2k is used
+ */
+
+select value tenk2.unique1
+from tenk1, tenk2
+where
+  tenk1.unique2 < 1
+and
+  tenk1.thousand /* +indexnl(idx_tenk1_1k, idx_tenk2_1k_2k) */ = tenk2.thousand
+order by tenk2.unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-10.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-10.sqlpp
new file mode 100644
index 0000000..f8d1e4b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-10.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+/*
+ * skip-index hint with a non-existent index name followed by an existing index name
+ * explain plan -> expect that idx_1k_2k index is used
+ */
+
+select value unique1
+from tenk
+where thousand /* +skip-index(UNKNOWN_INDEX, idx_1k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-11.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-11.sqlpp
new file mode 100644
index 0000000..82ea8b3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-11.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+/*
+ * disjunctive predicate is translated into an index join, use skip-index to disable index join.
+ * explain plan -> expect broadcast hash join
+ */
+
+select value unique1
+from tenk
+where thousand /* +skip-index */ = 0 or thousand = 1
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-12.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-12.sqlpp
new file mode 100644
index 0000000..f494d0e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-12.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+/*
+ * disjunctive predicate is translated into an index join, use skip-index to disable idx_1k for index join.
+ * explain plan -> expect index join using idx_1k_2k
+ */
+
+select value unique1
+from tenk
+where thousand /* +skip-index(idx_1k) */ = 0 or thousand = 1
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-3.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-3.sqlpp
new file mode 100644
index 0000000..2c99289
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-3.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+/*
+ * skip-index hint without parameters -> skip all indexes.
+ * explain plan -> expect that no indexes are used
+ */
+
+select value unique1
+from tenk
+where thousand /* +skip-index */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-4.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-4.sqlpp
new file mode 100644
index 0000000..a8a3d87
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-4.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+/*
+ * skip-index hint with parameters -> skip specified indexes.
+ * explain plan -> expect index idx_1k_2k to be used because idx_1k is skipped
+ */
+
+select value unique1
+from tenk
+where thousand /* +skip-index(idx_1k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-5.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-5.sqlpp
new file mode 100644
index 0000000..cb48d48
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-5.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+/*
+ * skip-index hint with parameters -> skip specified indexes.
+ * explain plan -> expect that no indexes are used because both idx_1k and idx_1k_2k are skipped
+ */
+
+select value unique1
+from tenk
+where thousand /* +skip-index(idx_1k, idx_1k_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-6.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-6.sqlpp
new file mode 100644
index 0000000..29ba360
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-6.sqlpp
@@ -0,0 +1,64 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+/*
+ * skip-index hint with parameters, composite index.
+ * skip all indexes for one predicate (idx_1k, idx_1k_2k on thousand)
+ * explain plan -> expect that idx_2k index is used for the other predicate (twothousand)
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +skip-index */ = 0
+and
+  twothousand = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-7.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-7.sqlpp
new file mode 100644
index 0000000..eb75790
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-7.sqlpp
@@ -0,0 +1,64 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+/*
+ * skip-index hint with parameters, composite index.
+ * skip specified indexes for one predicate (idx_1k on thousand)
+ * explain plan -> expect index intersection between idx_1k_2k (on thousand) and idx_2k (on twothousand)
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +skip-index(idx_1k) */ = 0
+and
+  twothousand = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-8.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-8.sqlpp
new file mode 100644
index 0000000..7cb196b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-8.sqlpp
@@ -0,0 +1,64 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+/*
+ * skip-index hint with parameters, composite index.
+ * skip specified indexes for both predicates (idx_1k on thousand, idx_2k on twothousand)
+ * explain plan -> only one index is used idx_1k_2k
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +skip-index(idx_1k) */ = 0
+and
+  twothousand /* +skip-index(idx_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-9.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-9.sqlpp
new file mode 100644
index 0000000..4016256
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-skip-index/hints-skip-index-9.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+/*
+ * skip-index hint with a non-existent index name -> ignored, the query does not fail
+ * explain plan -> expect that idx_1k is used
+ */
+
+select value unique1
+from tenk
+where thousand /* +skip-index(UNKNOWN_INDEX) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-10.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-10.sqlpp
new file mode 100644
index 0000000..fab0667
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-10.sqlpp
@@ -0,0 +1,67 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * use-index hint. ignore unknown indexes specified by the hint
+ * explain plan -> idx_1k is used
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +use-index(IDX_UNKNOWN) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-11.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-11.sqlpp
new file mode 100644
index 0000000..50036ae
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-11.sqlpp
@@ -0,0 +1,67 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * use-index hint. ignore unknown indexes specified by the hint
+ * explain plan -> idx_1k_2k is used
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +use-index(IDX_UNKNOWN, idx_1k_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-12.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-12.sqlpp
new file mode 100644
index 0000000..de279ae
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-12.sqlpp
@@ -0,0 +1,69 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * use-index hint. use specified indexes (idx_1k_2k, idx_5k_10k) even if unknown index names are present
+ * explain plan -> intersection between idx_1k_2k and idx_5k_10k
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +use-index(UNKNOWN_INDEX_1k, idx_1k_2k, UNKNOWN_INDEX_2k) */ = 0
+and
+  fivethous  /* +use-index(UNKNOWN_INDEX_5k, idx_5k_10k, UNKNOWN_INDEX_10k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-13.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-13.sqlpp
new file mode 100644
index 0000000..5235304
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-13.sqlpp
@@ -0,0 +1,67 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * use-index hint. specified index (idx_2k) exists, but is not applicable
+ * explain plan -> an applicable index is used instead (idx_1k)
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-14.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-14.sqlpp
new file mode 100644
index 0000000..a21ce6a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-14.sqlpp
@@ -0,0 +1,67 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * use-index hint. specified index (idx_1k_2k) exists, but is not applicable
+ * explain plan -> an applicable index is used instead (idx_2k)
+ */
+
+select value unique1
+from tenk
+where
+  twothousand /* +use-index(idx_1k_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-15.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-15.sqlpp
new file mode 100644
index 0000000..5fc946a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-15.sqlpp
@@ -0,0 +1,70 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * (corner case) use-index hint together with skip-index-hint for the same composite index.
+ * skip-index is processed first, so it excludes idx_1k and idx_1k_2k.
+ * explain plan -> idx_2k is used.
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +skip-index */ = 0
+and
+  twothousand /* +use-index(idx_1k_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-16.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-16.sqlpp
new file mode 100644
index 0000000..4d9f45f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-16.sqlpp
@@ -0,0 +1,70 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * (corner case) use-index hint together with skip-index-hint for the same composite index.
+ * skip-index is processed first, so it excludes idx_1k_2k.
+ * explain plan -> intersection between idx_1k and idx_2k.
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +skip-index(idx_1k_2k) */ = 0
+and
+  twothousand /* +use-index(idx_1k_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-17.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-17.sqlpp
new file mode 100644
index 0000000..383e0d4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-17.sqlpp
@@ -0,0 +1,71 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * (corner case) use-index hint together with skip-index-hint for the same composite index.
+ * skip-index is processed first, however it does not exclude idx_1k_2k
+ * because idx_1k_2k can be used with only the first predicate (thousand = 0).
+ * explain plan -> idx_1k_2k is used.
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_1k_2k) */ = 0
+and
+  twothousand /* +skip-index */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-3.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-3.sqlpp
new file mode 100644
index 0000000..a55efbd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-3.sqlpp
@@ -0,0 +1,66 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * use-index hint. use specified index (idx_1k_2k)
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_1k_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-4.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-4.sqlpp
new file mode 100644
index 0000000..6db8c05
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-4.sqlpp
@@ -0,0 +1,69 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * use-index hint. use specified indexes (idx_1k, idx_2k)
+ * explain plan -> intersection between idx_1k and idx_2k
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_1k) */ = 0
+and
+  twothousand /* +use-index(idx_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-5.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-5.sqlpp
new file mode 100644
index 0000000..6fbecfb
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-5.sqlpp
@@ -0,0 +1,69 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * use-index hint. use specified indexes (idx_1k_2k, idx_5k_10k)
+ * explain plan -> intersection between idx_1k_2k and idx_2k_5k
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_1k_2k) */ = 0
+and
+  fivethous /* +use-index(idx_5k_10k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-6.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-6.sqlpp
new file mode 100644
index 0000000..ea9f30b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-6.sqlpp
@@ -0,0 +1,69 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * use-index hint. use specified composite index (idx_1k_2k)
+ * explain plan -> use idx_1k_2k
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_1k_2k) */ = 0
+and
+  twothousand = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-7.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-7.sqlpp
new file mode 100644
index 0000000..7096240
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-7.sqlpp
@@ -0,0 +1,69 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * use-index hint. use specified composite index (idx_1k_2k)
+ * explain plan -> use idx_1k_2k
+ */
+
+select value unique1
+from tenk
+where
+  thousand = 0
+and
+  twothousand /* +use-index(idx_1k_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-8.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-8.sqlpp
new file mode 100644
index 0000000..fa54dc6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-8.sqlpp
@@ -0,0 +1,69 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * use-index hint. use specified indexes (idx_1k_2k, idx_2k_5k)
+ * explain plan -> intersection between idx_1k_2k and idx_2k_5k
+ */
+
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_1k_2k) */ = 0
+and
+  twothousand /* +use-index(idx_2k_5k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-9.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-9.sqlpp
new file mode 100644
index 0000000..fa6bb12
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/hints-use-index/hints-use-index-9.sqlpp
@@ -0,0 +1,69 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
+
+create index idx_5k on tenk(fivethous);
+
+create index idx_5k_10k on tenk(fivethous, tenthous);
+
+/*
+ * use-index hint. use specified indexes (idx_1k_2k, idx_2k_5k)
+ * explain plan -> intersection between idx_1k_2k and idx_2k_5k
+ */
+
+select value unique1
+from tenk
+where
+  thousand = 0
+and
+  twothousand /* +use-index(idx_1k_2k, idx_2k_5k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-3.plan
new file mode 100644
index 0000000..9627dce
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-3.plan
@@ -0,0 +1,25 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$37(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$37(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk2.tenk2)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$43(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk2.idx_tenk2_1k_2k)  |PARTITIONED|
+                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ASSIGN  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- BTREE_SEARCH (test.tenk1.tenk1)  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-4.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-4.plan
new file mode 100644
index 0000000..758182a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-4.plan
@@ -0,0 +1,25 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$37(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$37(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk2.tenk2)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$42(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk2.idx_tenk2_1k)  |PARTITIONED|
+                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ASSIGN  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- BTREE_SEARCH (test.tenk1.tenk1)  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-5.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-5.plan
new file mode 100644
index 0000000..9627dce
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-5.plan
@@ -0,0 +1,25 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$37(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$37(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk2.tenk2)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$43(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk2.idx_tenk2_1k_2k)  |PARTITIONED|
+                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ASSIGN  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- BTREE_SEARCH (test.tenk1.tenk1)  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-6.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-6.plan
new file mode 100644
index 0000000..758182a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-6.plan
@@ -0,0 +1,25 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$37(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$37(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk2.tenk2)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$42(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk2.idx_tenk2_1k)  |PARTITIONED|
+                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ASSIGN  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- BTREE_SEARCH (test.tenk1.tenk1)  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-7.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-7.plan
new file mode 100644
index 0000000..9627dce
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/hints-indexnl-params/hints-indexnl-params-7.plan
@@ -0,0 +1,25 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$37(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$37(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk2.tenk2)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$43(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk2.idx_tenk2_1k_2k)  |PARTITIONED|
+                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ASSIGN  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- BTREE_SEARCH (test.tenk1.tenk1)  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-10.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-10.plan
new file mode 100644
index 0000000..7af465d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-10.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$19(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$19(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$24(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-11.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-11.plan
new file mode 100644
index 0000000..51bb0f2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-11.plan
@@ -0,0 +1,18 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- HYBRID_HASH_JOIN [$$20][$$22]  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ASSIGN  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- DATASOURCE_SCAN (test.tenk)  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+              -- BROADCAST_EXCHANGE  |PARTITIONED|
+                -- UNNEST  |UNPARTITIONED|
+                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-12.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-12.plan
new file mode 100644
index 0000000..55140ef
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-12.plan
@@ -0,0 +1,20 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$27(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |UNPARTITIONED|
+                                    -- UNNEST  |UNPARTITIONED|
+                                      -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-3.plan
new file mode 100644
index 0000000..f958053
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-3.plan
@@ -0,0 +1,12 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$19(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$19(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- DATASOURCE_SCAN (test.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-4.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-4.plan
new file mode 100644
index 0000000..7af465d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-4.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$19(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$19(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$24(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-5.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-5.plan
new file mode 100644
index 0000000..f958053
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-5.plan
@@ -0,0 +1,12 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$19(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$19(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- DATASOURCE_SCAN (test.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-6.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-6.plan
new file mode 100644
index 0000000..5a83b61
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-6.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$27(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_2k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-7.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-7.plan
new file mode 100644
index 0000000..a84e5c8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-7.plan
@@ -0,0 +1,30 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- INTERSECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$30(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$34(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_2k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-8.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-8.plan
new file mode 100644
index 0000000..1d31f3b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-8.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$30(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-9.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-9.plan
new file mode 100644
index 0000000..102a5e7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-skip-index/hints-skip-index-9.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$19(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$19(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_1k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-10.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-10.plan
new file mode 100644
index 0000000..102a5e7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-10.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$19(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$19(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_1k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-11.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-11.plan
new file mode 100644
index 0000000..7af465d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-11.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$19(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$19(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$24(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-12.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-12.plan
new file mode 100644
index 0000000..d68a5a1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-12.plan
@@ -0,0 +1,37 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- INTERSECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$28(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- REPLICATE  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$33(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_5k_10k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- REPLICATE  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-13.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-13.plan
new file mode 100644
index 0000000..102a5e7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-13.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$19(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$19(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_1k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-14.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-14.plan
new file mode 100644
index 0000000..8f99941
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-14.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$19(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$19(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_2k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-15.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-15.plan
new file mode 100644
index 0000000..5a83b61
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-15.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$27(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_2k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-16.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-16.plan
new file mode 100644
index 0000000..ce4ed8e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-16.plan
@@ -0,0 +1,37 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- INTERSECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$27(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_1k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- REPLICATE  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$31(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_2k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- REPLICATE  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-17.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-17.plan
new file mode 100644
index 0000000..f04dd37
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-17.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$28(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-3.plan
new file mode 100644
index 0000000..7af465d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-3.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$19(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$19(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$24(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-4.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-4.plan
new file mode 100644
index 0000000..ce4ed8e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-4.plan
@@ -0,0 +1,37 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- INTERSECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$27(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_1k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- REPLICATE  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$31(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_2k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- REPLICATE  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-5.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-5.plan
new file mode 100644
index 0000000..d68a5a1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-5.plan
@@ -0,0 +1,37 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- INTERSECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$28(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- REPLICATE  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$33(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_5k_10k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- REPLICATE  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-6.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-6.plan
new file mode 100644
index 0000000..1d31f3b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-6.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$30(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-7.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-7.plan
new file mode 100644
index 0000000..1d31f3b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-7.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$30(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-8.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-8.plan
new file mode 100644
index 0000000..8594229
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-8.plan
@@ -0,0 +1,30 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- INTERSECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$30(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$35(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_2k_5k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-9.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-9.plan
new file mode 100644
index 0000000..8594229
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/hints-use-index/hints-use-index-9.plan
@@ -0,0 +1,30 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$23(ASC) ]  |PARTITIONED|
+    -- STABLE_SORT [$$23(ASC)]  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- BTREE_SEARCH (test.tenk.tenk)  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- INTERSECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$30(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_1k_2k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$35(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.tenk.idx_2k_5k)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.1.ddl.sqlpp
new file mode 100644
index 0000000..944f1a0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.1.ddl.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk1(tenkType) primary key unique2;
+
+create index idx_tenk1_1k on tenk1(thousand);
+
+create dataset tenk2(tenkType) primary key unique2;
+
+create index idx_tenk2_1k on tenk2(thousand);
+
+create index idx_tenk2_1k_2k on tenk2(thousand, twothousand);
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.2.update.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.2.update.sqlpp
index c026883..7ccdfa1 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.2.update.sqlpp
@@ -16,19 +16,9 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+load dataset tenk1 using localfs ((`path`=`asterix_nc1://data/tenk.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+load dataset tenk2 using localfs ((`path`=`asterix_nc1://data/tenk.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.3.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.3.query.sqlpp
index c026883..c07004d 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.3.query.sqlpp
@@ -16,19 +16,17 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * indexnl hint. use specified index (idx_tenk2_1k_2k)
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value tenk2.unique1
+from tenk1, tenk2
+where
+  tenk1.unique2 < 1
+and
+  tenk1.thousand /* +indexnl(idx_tenk2_1k_2k) */ = tenk2.thousand
+order by tenk2.unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.4.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.4.query.sqlpp
index c026883..9dca70d 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.4.query.sqlpp
@@ -16,19 +16,18 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * indexnl hint. ignore unknown index
+ * explain plan -> idx_tenk2_1k is used
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value tenk2.unique1
+from tenk1, tenk2
+where
+  tenk1.unique2 < 1
+and
+  tenk1.thousand /* +indexnl(IDX_UNKNOWN) */ = tenk2.thousand
+order by tenk2.unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.5.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.5.query.sqlpp
index c026883..29fc399 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.5.query.sqlpp
@@ -16,19 +16,18 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * indexnl hint. ignore unknown indexes, use specified index
+ * explain plan -> idx_tenk2_1k_2k is used
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value tenk2.unique1
+from tenk1, tenk2
+where
+  tenk1.unique2 < 1
+and
+  tenk1.thousand /* +indexnl(IDX_UNKNOWN_1, idx_tenk2_1k_2k, IDX_UNKNOWN_2) */ = tenk2.thousand
+order by tenk2.unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.6.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.6.query.sqlpp
index c026883..84735a8 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.6.query.sqlpp
@@ -16,19 +16,18 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * indexnl hint. ignore inapplicable index
+ * explain plan -> idx_tenk2_1k is used
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value tenk2.unique1
+from tenk1, tenk2
+where
+  tenk1.unique2 < 1
+and
+  tenk1.thousand /* +indexnl(idx_tenk1_1k) */ = tenk2.thousand
+order by tenk2.unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.7.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.7.query.sqlpp
index c026883..2534359 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/hints-indexnl-params/hints-indexnl-params.7.query.sqlpp
@@ -16,19 +16,18 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * indexnl hint. ignore inapplicable index, use specified index
+ * explain plan -> idx_tenk2_1k_2k is used
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value tenk2.unique1
+from tenk1, tenk2
+where
+  tenk1.unique2 < 1
+and
+  tenk1.thousand /* +indexnl(idx_tenk1_1k, idx_tenk2_1k_2k) */ = tenk2.thousand
+order by tenk2.unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.1.ddl.sqlpp
new file mode 100644
index 0000000..6272067
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.1.ddl.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.10.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.10.query.sqlpp
index c026883..323508f 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.10.query.sqlpp
@@ -16,19 +16,15 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * skip-index hint with a non-existent index name followed by an existing index name
+ * explain plan -> expect that idx_1k_2k index is used
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where thousand /* +skip-index(UNKNOWN_INDEX, idx_1k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.2.update.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.2.update.sqlpp
index c026883..a309240 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.2.update.sqlpp
@@ -16,19 +16,7 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
-
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+load dataset tenk using localfs ((`path`=`asterix_nc1://data/tenk.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.3.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.3.query.sqlpp
index c026883..a660fd6 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.3.query.sqlpp
@@ -16,19 +16,15 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * skip-index hint without parameters -> skip all indexes.
+ * explain plan -> expect that no indexes are used
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where thousand /* +skip-index */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.4.query.sqlpp
similarity index 63%
rename from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
rename to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.4.query.sqlpp
index c026883..acd78c5 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.4.query.sqlpp
@@ -16,19 +16,15 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * skip-index hint with parameters -> skip specified indexes.
+ * explain plan -> expect index idx_1k_2k to be used because idx_1k is skipped
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where thousand /* +skip-index(idx_1k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.5.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.5.query.sqlpp
index c026883..73f388c 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.5.query.sqlpp
@@ -16,19 +16,15 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * skip-index hint with parameters -> skip specified indexes.
+ * explain plan -> expect that no indexes are used because both idx_1k and idx_1k_2k are skipped
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where thousand /* +skip-index(idx_1k, idx_1k_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.6.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.6.query.sqlpp
index c026883..daa565f 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.6.query.sqlpp
@@ -16,19 +16,19 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * skip-index hint with parameters, composite index.
+ * skip all indexes for one predicate (idx_1k, idx_1k_2k on thousand)
+ * explain plan -> expect that idx_2k index is used for the other predicate (twothousand)
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +skip-index */ = 0
+and
+  twothousand = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.7.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.7.query.sqlpp
index c026883..b228d2f 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.7.query.sqlpp
@@ -16,19 +16,19 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * skip-index hint with parameters, composite index.
+ * skip specified indexes for one predicate (idx_1k on thousand)
+ * explain plan -> expect index intersection between idx_1k_2k (on thousand) and idx_2k (on twothousand)
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +skip-index(idx_1k) */ = 0
+and
+  twothousand = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.8.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.8.query.sqlpp
index c026883..ce49c9e 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.8.query.sqlpp
@@ -16,19 +16,19 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * skip-index hint with parameters, composite index.
+ * skip specified indexes for both predicates (idx_1k on thousand, idx_2k on twothousand)
+ * explain plan -> only one index is used idx_1k_2k
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +skip-index(idx_1k) */ = 0
+and
+  twothousand /* +skip-index(idx_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.9.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.9.query.sqlpp
index c026883..b019f8e 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-skip-index/hints-skip-index.9.query.sqlpp
@@ -16,19 +16,15 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * skip-index hint with a non-existent index name -> ignored, the query does not fail
+ * explain plan -> expect that idx_1k is used
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where thousand /* +skip-index(UNKNOWN_INDEX) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.1.ddl.sqlpp
new file mode 100644
index 0000000..d38ac93
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.1.ddl.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothousand     : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+create index idx_1k on tenk(thousand);
+
+create index idx_1k_2k on tenk(thousand, twothousand);
+
+create index idx_2k on tenk(twothousand);
+
+create index idx_2k_5k on tenk(twothousand, fivethous);
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.10.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.10.query.sqlpp
index c026883..c8e0188 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.10.query.sqlpp
@@ -16,19 +16,16 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * use-index hint. ignore unknown indexes specified by the hint
+ * explain plan -> idx_1k is used
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +use-index(IDX_UNKNOWN) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.11.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.11.query.sqlpp
index c026883..86cdabc 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.11.query.sqlpp
@@ -16,19 +16,16 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * use-index hint. ignore unknown indexes specified by the hint
+ * explain plan -> idx_1k_2k is used
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +use-index(IDX_UNKNOWN, idx_1k_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.12.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.12.query.sqlpp
index c026883..8ba1c97 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.12.query.sqlpp
@@ -16,19 +16,18 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * use-index hint. use specified indexes (idx_1k_2k, idx_5k_10k) even if unknown index names are present
+ * explain plan -> intersection between idx_1k_2k and idx_5k_10k
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +use-index(UNKNOWN_INDEX_1k, idx_1k_2k, UNKNOWN_INDEX_2k) */ = 0
+and
+  fivethous  /* +use-index(UNKNOWN_INDEX_5k, idx_5k_10k, UNKNOWN_INDEX_10k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.13.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.13.query.sqlpp
index c026883..a282d19 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.13.query.sqlpp
@@ -16,19 +16,16 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * use-index hint. specified index (idx_2k) exists, but is not applicable
+ * explain plan -> an applicable index is used instead (idx_1k)
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.14.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.14.query.sqlpp
index c026883..323681f 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.14.query.sqlpp
@@ -16,19 +16,16 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * use-index hint. specified index (idx_1k_2k) exists, but is not applicable
+ * explain plan -> an applicable index is used instead (idx_2k)
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  twothousand /* +use-index(idx_1k_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.15.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.15.query.sqlpp
index c026883..88d53ea 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.15.query.sqlpp
@@ -16,19 +16,19 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * (corner case) use-index hint together with skip-index-hint for the same composite index.
+ * skip-index is processed first, so it excludes idx_1k and idx_1k_2k.
+ * explain plan -> idx_2k is used.
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +skip-index */ = 0
+and
+  twothousand /* +use-index(idx_1k_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.16.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.16.query.sqlpp
index c026883..ffdb7d9 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.16.query.sqlpp
@@ -16,19 +16,19 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * (corner case) use-index hint together with skip-index-hint for the same composite index.
+ * skip-index is processed first, so it excludes idx_1k_2k.
+ * explain plan -> intersection between idx_1k and idx_2k.
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +skip-index(idx_1k_2k) */ = 0
+and
+  twothousand /* +use-index(idx_1k_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.17.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.17.query.sqlpp
index c026883..45be397 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.17.query.sqlpp
@@ -16,19 +16,20 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * (corner case) use-index hint together with skip-index-hint for the same composite index.
+ * skip-index is processed first, however it does not exclude idx_1k_2k
+ * because idx_1k_2k can be used with only the first predicate (thousand = 0).
+ * explain plan -> idx_1k_2k is used.
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_1k_2k) */ = 0
+and
+  twothousand /* +skip-index */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.2.update.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.2.update.sqlpp
index c026883..a309240 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.2.update.sqlpp
@@ -16,19 +16,7 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
-
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+load dataset tenk using localfs ((`path`=`asterix_nc1://data/tenk.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.3.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.3.query.sqlpp
index c026883..e4ef43c 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.3.query.sqlpp
@@ -16,19 +16,15 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * use-index hint. use specified index (idx_1k_2k)
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_1k_2k) */ = 0
+order by unique1;
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.4.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.4.query.sqlpp
index c026883..7e683ee 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.4.query.sqlpp
@@ -16,19 +16,18 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * use-index hint. use specified indexes (idx_1k, idx_2k)
+ * explain plan -> intersection between idx_1k and idx_2k
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_1k) */ = 0
+and
+  twothousand /* +use-index(idx_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.5.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.5.query.sqlpp
index c026883..aa321e4 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.5.query.sqlpp
@@ -16,19 +16,18 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * use-index hint. use specified indexes (idx_1k_2k, idx_5k_10k)
+ * explain plan -> intersection between idx_1k_2k and idx_2k_5k
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_1k_2k) */ = 0
+and
+  fivethous /* +use-index(idx_5k_10k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.6.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.6.query.sqlpp
index c026883..296dcf0 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.6.query.sqlpp
@@ -16,19 +16,18 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * use-index hint. use specified composite index (idx_1k_2k)
+ * explain plan -> use idx_1k_2k
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_1k_2k) */ = 0
+and
+  twothousand = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.7.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.7.query.sqlpp
index c026883..763500a 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.7.query.sqlpp
@@ -16,19 +16,18 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * use-index hint. use specified composite index (idx_1k_2k)
+ * explain plan -> use idx_1k_2k
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand = 0
+and
+  twothousand /* +use-index(idx_1k_2k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.8.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.8.query.sqlpp
index c026883..07ac082 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.8.query.sqlpp
@@ -16,19 +16,18 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * use-index hint. use specified indexes (idx_1k_2k, idx_2k_5k)
+ * explain plan -> intersection between idx_1k_2k and idx_2k_5k
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand /* +use-index(idx_1k_2k) */ = 0
+and
+  twothousand /* +use-index(idx_2k_5k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.9.query.sqlpp
similarity index 63%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.9.query.sqlpp
index c026883..9730814 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/hints-use-index/hints-use-index.9.query.sqlpp
@@ -16,19 +16,18 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+use test;
 
-    public static final String HINT_STRING = "indexnl";
+/*
+ * use-index hint. use specified indexes (idx_1k_2k, idx_2k_5k)
+ * explain plan -> intersection between idx_1k_2k and idx_2k_5k
+ */
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
-
-    private IndexedNLJoinExpressionAnnotation() {
-    }
-
-    @Override
-    public String toString() {
-        return HINT_STRING;
-    }
-}
+select value unique1
+from tenk
+where
+  thousand = 0
+and
+  twothousand /* +use-index(idx_1k_2k, idx_2k_5k) */ = 0
+order by unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.3.adm
new file mode 100644
index 0000000..7336a43
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.3.adm
@@ -0,0 +1,10 @@
+800
+1800
+2800
+3800
+4800
+5800
+6800
+7800
+8800
+9800
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.4.adm
new file mode 100644
index 0000000..7336a43
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.4.adm
@@ -0,0 +1,10 @@
+800
+1800
+2800
+3800
+4800
+5800
+6800
+7800
+8800
+9800
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.5.adm
new file mode 100644
index 0000000..7336a43
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.5.adm
@@ -0,0 +1,10 @@
+800
+1800
+2800
+3800
+4800
+5800
+6800
+7800
+8800
+9800
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.6.adm
new file mode 100644
index 0000000..7336a43
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.6.adm
@@ -0,0 +1,10 @@
+800
+1800
+2800
+3800
+4800
+5800
+6800
+7800
+8800
+9800
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.7.adm
new file mode 100644
index 0000000..7336a43
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/hints-indexnl-params/hints-indexnl-params.7.adm
@@ -0,0 +1,10 @@
+800
+1800
+2800
+3800
+4800
+5800
+6800
+7800
+8800
+9800
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.10.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.10.adm
new file mode 100644
index 0000000..17cce63
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.10.adm
@@ -0,0 +1,10 @@
+0
+1000
+2000
+3000
+4000
+5000
+6000
+7000
+8000
+9000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.3.adm
new file mode 100644
index 0000000..17cce63
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.3.adm
@@ -0,0 +1,10 @@
+0
+1000
+2000
+3000
+4000
+5000
+6000
+7000
+8000
+9000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.4.adm
new file mode 100644
index 0000000..17cce63
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.4.adm
@@ -0,0 +1,10 @@
+0
+1000
+2000
+3000
+4000
+5000
+6000
+7000
+8000
+9000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.5.adm
new file mode 100644
index 0000000..17cce63
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.5.adm
@@ -0,0 +1,10 @@
+0
+1000
+2000
+3000
+4000
+5000
+6000
+7000
+8000
+9000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.6.adm
new file mode 100644
index 0000000..7d3b8c3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.6.adm
@@ -0,0 +1,5 @@
+0
+2000
+4000
+6000
+8000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.7.adm
new file mode 100644
index 0000000..7d3b8c3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.7.adm
@@ -0,0 +1,5 @@
+0
+2000
+4000
+6000
+8000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.8.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.8.adm
new file mode 100644
index 0000000..7d3b8c3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.8.adm
@@ -0,0 +1,5 @@
+0
+2000
+4000
+6000
+8000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.9.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.9.adm
new file mode 100644
index 0000000..17cce63
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-skip-index/hints-skip-index.9.adm
@@ -0,0 +1,10 @@
+0
+1000
+2000
+3000
+4000
+5000
+6000
+7000
+8000
+9000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.10.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.10.adm
new file mode 100644
index 0000000..17cce63
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.10.adm
@@ -0,0 +1,10 @@
+0
+1000
+2000
+3000
+4000
+5000
+6000
+7000
+8000
+9000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.11.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.11.adm
new file mode 100644
index 0000000..17cce63
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.11.adm
@@ -0,0 +1,10 @@
+0
+1000
+2000
+3000
+4000
+5000
+6000
+7000
+8000
+9000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.12.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.12.adm
new file mode 100644
index 0000000..c137048
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.12.adm
@@ -0,0 +1,2 @@
+0
+5000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.13.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.13.adm
new file mode 100644
index 0000000..17cce63
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.13.adm
@@ -0,0 +1,10 @@
+0
+1000
+2000
+3000
+4000
+5000
+6000
+7000
+8000
+9000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.14.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.14.adm
new file mode 100644
index 0000000..7d3b8c3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.14.adm
@@ -0,0 +1,5 @@
+0
+2000
+4000
+6000
+8000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.15.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.15.adm
new file mode 100644
index 0000000..7d3b8c3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.15.adm
@@ -0,0 +1,5 @@
+0
+2000
+4000
+6000
+8000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.16.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.16.adm
new file mode 100644
index 0000000..7d3b8c3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.16.adm
@@ -0,0 +1,5 @@
+0
+2000
+4000
+6000
+8000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.17.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.17.adm
new file mode 100644
index 0000000..7d3b8c3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.17.adm
@@ -0,0 +1,5 @@
+0
+2000
+4000
+6000
+8000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.3.adm
new file mode 100644
index 0000000..17cce63
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.3.adm
@@ -0,0 +1,10 @@
+0
+1000
+2000
+3000
+4000
+5000
+6000
+7000
+8000
+9000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.4.adm
new file mode 100644
index 0000000..7d3b8c3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.4.adm
@@ -0,0 +1,5 @@
+0
+2000
+4000
+6000
+8000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.5.adm
new file mode 100644
index 0000000..c137048
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.5.adm
@@ -0,0 +1,2 @@
+0
+5000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.6.adm
new file mode 100644
index 0000000..7d3b8c3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.6.adm
@@ -0,0 +1,5 @@
+0
+2000
+4000
+6000
+8000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.7.adm
new file mode 100644
index 0000000..7d3b8c3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.7.adm
@@ -0,0 +1,5 @@
+0
+2000
+4000
+6000
+8000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.8.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.8.adm
new file mode 100644
index 0000000..7d3b8c3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.8.adm
@@ -0,0 +1,5 @@
+0
+2000
+4000
+6000
+8000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.9.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.9.adm
new file mode 100644
index 0000000..7d3b8c3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/hints-use-index/hints-use-index.9.adm
@@ -0,0 +1,5 @@
+0
+2000
+4000
+6000
+8000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index df8de8d..f6113c4 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -5881,6 +5881,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="index-join">
+      <compilation-unit name="hints-indexnl-params">
+        <output-dir compare="Text">hints-indexnl-params</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="index-join">
       <compilation-unit name="rtree-spatial-intersect-point_01">
         <output-dir compare="Text">rtree-spatial-intersect-point_01</output-dir>
       </compilation-unit>
@@ -6153,6 +6158,16 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="index-selection">
+      <compilation-unit name="hints-skip-index">
+        <output-dir compare="Text">hints-skip-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="index-selection">
+      <compilation-unit name="hints-use-index">
+        <output-dir compare="Text">hints-use-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="index-selection">
       <compilation-unit name="inverted-index-ngram-edit-distance-with-two-ngram-index">
         <output-dir compare="Text">inverted-index-ngram-edit-distance-with-two-ngram-index</output-dir>
       </compilation-unit>
@@ -14066,9 +14081,9 @@
         <output-dir compare="Text">inapplicable-hint-warning</output-dir>
         <expected-warn>HYR10006: Could not apply Group By hint: hash</expected-warn>
         <expected-warn>ASX1107: Unexpected hint: indexnl. "hash" expected at this location</expected-warn>
-        <expected-warn>ASX1107: Unexpected hint: hash. "indexnl", "skip-index", "hash-bcast" expected at this location</expected-warn>
-        <expected-warn>ASX1107: Unexpected hint: auto. "indexnl", "skip-index" expected at this location</expected-warn>
-        <expected-warn>ASX1107: Unexpected hint: hash. "indexnl", "skip-index", "range" expected at this location</expected-warn>
+        <expected-warn>ASX1107: Unexpected hint: hash. "hash-bcast", "indexnl", "skip-index", "use-index" expected at this location</expected-warn>
+        <expected-warn>ASX1107: Unexpected hint: auto. "indexnl", "skip-index", "use-index" expected at this location</expected-warn>
+        <expected-warn>ASX1107: Unexpected hint: hash. "indexnl", "range", "skip-index", "use-index" expected at this location</expected-warn>
         <expected-warn>ASX1107: Unexpected hint: hash. None expected at this location</expected-warn>
       </compilation-unit>
     </test-case>
@@ -14089,9 +14104,9 @@
       <compilation-unit name="unknown-hint-warning">
         <output-dir compare="Text">unknown-hint-warning</output-dir>
         <expected-warn>ASX1107: Unexpected hint: unknown_hint_groupby. "hash" expected at this location</expected-warn>
-        <expected-warn>ASX1107: Unexpected hint: unknown_hint_relexpr. "indexnl", "skip-index", "hash-bcast" expected at this location</expected-warn>
-        <expected-warn>ASX1107: Unexpected hint: unknown_hint_between. "indexnl", "skip-index" expected at this location</expected-warn>
-        <expected-warn>ASX1107: Unexpected hint: unknown_hint_funcall. "indexnl", "skip-index", "range" expected at this location</expected-warn>
+        <expected-warn>ASX1107: Unexpected hint: unknown_hint_relexpr. "hash-bcast", "indexnl", "skip-index", "use-index" expected at this location</expected-warn>
+        <expected-warn>ASX1107: Unexpected hint: unknown_hint_between. "indexnl", "skip-index", "use-index" expected at this location</expected-warn>
+        <expected-warn>ASX1107: Unexpected hint: unknown_hint_funcall. "indexnl", "range", "skip-index", "use-index" expected at this location</expected-warn>
         <expected-warn>ASX1107: Unexpected hint: unknown_hint_elsewhere. None expected at this location</expected-warn>
       </compilation-unit>
     </test-case>
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/annotations/AbstractExpressionAnnotationWithIndexNames.java
similarity index 60%
copy from hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
copy to asterixdb/asterix-common/src/main/java/org/apache/asterix/common/annotations/AbstractExpressionAnnotationWithIndexNames.java
index c026883..4833aa1 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/expressions/IndexedNLJoinExpressionAnnotation.java
+++ b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/annotations/AbstractExpressionAnnotationWithIndexNames.java
@@ -16,19 +16,22 @@
  * specific language governing permissions and limitations
  * under the License.
  */
-package org.apache.hyracks.algebricks.core.algebra.expressions;
 
-public final class IndexedNLJoinExpressionAnnotation implements IExpressionAnnotation {
+package org.apache.asterix.common.annotations;
 
-    public static final String HINT_STRING = "indexnl";
+import java.util.Collection;
 
-    public static final IndexedNLJoinExpressionAnnotation INSTANCE = new IndexedNLJoinExpressionAnnotation();
+import org.apache.hyracks.algebricks.core.algebra.expressions.IExpressionAnnotation;
 
-    private IndexedNLJoinExpressionAnnotation() {
+public abstract class AbstractExpressionAnnotationWithIndexNames implements IExpressionAnnotation {
+
+    protected final Collection<String> indexNames; //TODO:use Set<>
+
+    protected AbstractExpressionAnnotationWithIndexNames(Collection<String> indexNames) {
+        this.indexNames = indexNames;
     }
 
-    @Override
-    public String toString() {
-        return HINT_STRING;
+    public Collection<String> getIndexNames() {
+        return indexNames;
     }
 }
diff --git a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/annotations/IndexedNLJoinExpressionAnnotation.java b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/annotations/IndexedNLJoinExpressionAnnotation.java
new file mode 100644
index 0000000..d2d2d20
--- /dev/null
+++ b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/annotations/IndexedNLJoinExpressionAnnotation.java
@@ -0,0 +1,45 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.asterix.common.annotations;
+
+import java.util.Collection;
+
+public final class IndexedNLJoinExpressionAnnotation extends AbstractExpressionAnnotationWithIndexNames {
+
+    public static final String HINT_STRING = "indexnl";
+
+    public static final IndexedNLJoinExpressionAnnotation INSTANCE_ANY_INDEX =
+            new IndexedNLJoinExpressionAnnotation(null);
+
+    private IndexedNLJoinExpressionAnnotation(Collection<String> indexNames) {
+        super(indexNames);
+    }
+
+    public static IndexedNLJoinExpressionAnnotation newInstance(Collection<String> indexNames) {
+        if (indexNames == null || indexNames.isEmpty()) {
+            throw new IllegalArgumentException();
+        }
+        return new IndexedNLJoinExpressionAnnotation(indexNames);
+    }
+
+    @Override
+    public String toString() {
+        return indexNames == null ? HINT_STRING : HINT_STRING + indexNames;
+    }
+}
diff --git a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/annotations/SecondaryIndexSearchPreferenceAnnotation.java b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/annotations/SecondaryIndexSearchPreferenceAnnotation.java
new file mode 100644
index 0000000..40e53d8
--- /dev/null
+++ b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/annotations/SecondaryIndexSearchPreferenceAnnotation.java
@@ -0,0 +1,43 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.asterix.common.annotations;
+
+import java.util.Collection;
+
+public final class SecondaryIndexSearchPreferenceAnnotation extends AbstractExpressionAnnotationWithIndexNames {
+
+    public static final String HINT_STRING = "use-index";
+
+    private SecondaryIndexSearchPreferenceAnnotation(Collection<String> indexNames) {
+        super(indexNames);
+    }
+
+    public static SecondaryIndexSearchPreferenceAnnotation newInstance(Collection<String> indexNames) {
+        if (indexNames == null || indexNames.isEmpty()) {
+            throw new IllegalArgumentException();
+        }
+        return new SecondaryIndexSearchPreferenceAnnotation(indexNames);
+    }
+
+    @Override
+    public String toString() {
+        return HINT_STRING + indexNames;
+    }
+}
\ No newline at end of file
diff --git a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/annotations/SkipSecondaryIndexSearchExpressionAnnotation.java b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/annotations/SkipSecondaryIndexSearchExpressionAnnotation.java
index 3cc34f5..431366a 100644
--- a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/annotations/SkipSecondaryIndexSearchExpressionAnnotation.java
+++ b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/annotations/SkipSecondaryIndexSearchExpressionAnnotation.java
@@ -18,20 +18,28 @@
  */
 package org.apache.asterix.common.annotations;
 
-import org.apache.hyracks.algebricks.core.algebra.expressions.IExpressionAnnotation;
+import java.util.Collection;
 
-public final class SkipSecondaryIndexSearchExpressionAnnotation implements IExpressionAnnotation {
+public final class SkipSecondaryIndexSearchExpressionAnnotation extends AbstractExpressionAnnotationWithIndexNames {
 
     public static final String HINT_STRING = "skip-index";
 
-    public static final SkipSecondaryIndexSearchExpressionAnnotation INSTANCE =
-            new SkipSecondaryIndexSearchExpressionAnnotation();
+    public static final SkipSecondaryIndexSearchExpressionAnnotation INSTANCE_ANY_INDEX =
+            new SkipSecondaryIndexSearchExpressionAnnotation(null);
 
-    private SkipSecondaryIndexSearchExpressionAnnotation() {
+    private SkipSecondaryIndexSearchExpressionAnnotation(Collection<String> indexNames) {
+        super(indexNames);
+    }
+
+    public static SkipSecondaryIndexSearchExpressionAnnotation newInstance(Collection<String> indexNames) {
+        if (indexNames == null || indexNames.isEmpty()) {
+            throw new IllegalArgumentException();
+        }
+        return new SkipSecondaryIndexSearchExpressionAnnotation(indexNames);
     }
 
     @Override
     public String toString() {
-        return HINT_STRING;
+        return indexNames == null ? HINT_STRING : HINT_STRING + indexNames;
     }
 }
diff --git a/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf b/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf
index 9904d92..886efc9 100644
--- a/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf
+++ b/asterixdb/asterix-doc/src/main/grammar/sqlpp.ebnf
@@ -82,7 +82,7 @@
 
 GroupAsClause ::= "GROUP AS" Identifier
 
-Selection ::= WithClause? QueryBlock UnionOption* OrderByClause? ( LimitClause | | OffsetClause )?
+Selection ::= WithClause? QueryBlock UnionOption* OrderByClause? ( LimitClause | OffsetClause )?
 
 UnionOption ::= "UNION ALL" (QueryBlock | Subquery)
 
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/parser/SqlppHint.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/parser/SqlppHint.java
index 27e2fa0..ea199ea 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/parser/SqlppHint.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/parser/SqlppHint.java
@@ -41,6 +41,7 @@
     LIST_VAL_FILE_HINT("list-val-file"),
     RANGE_HINT("range"),
     SKIP_SECONDARY_INDEX_SEARCH_HINT("skip-index"),
+    USE_SECONDARY_INDEX_SEARCH_HINT("use-index"),
     VAL_FILE_HINT("val-files"),
     VAL_FILE_SAME_INDEX_HINT("val-file-same-idx"),
     GEN_FIELDS_HINT("gen-fields"),
@@ -53,8 +54,7 @@
     private final String id;
 
     SqlppHint(String id) {
-        Objects.requireNonNull(id);
-        this.id = id;
+        this.id = Objects.requireNonNull(id);
     }
 
     public String getIdentifier() {
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index a4995da..645fc27 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -56,10 +56,12 @@
 import org.apache.asterix.common.annotations.FieldValFileDataGen;
 import org.apache.asterix.common.annotations.FieldValFileSameIndexDataGen;
 import org.apache.asterix.common.annotations.IRecordFieldDataGen;
+import org.apache.asterix.common.annotations.IndexedNLJoinExpressionAnnotation;
 import org.apache.asterix.common.annotations.InsertRandIntDataGen;
 import org.apache.asterix.common.annotations.ListDataGen;
 import org.apache.asterix.common.annotations.ListValFileDataGen;
 import org.apache.asterix.common.annotations.RangeAnnotation;
+import org.apache.asterix.common.annotations.SecondaryIndexSearchPreferenceAnnotation;
 import org.apache.asterix.common.annotations.SkipSecondaryIndexSearchExpressionAnnotation;
 import org.apache.asterix.common.annotations.TypeDataGen;
 import org.apache.asterix.common.annotations.UndeclaredFieldsDataGen;
@@ -202,7 +204,6 @@
 import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
 import org.apache.hyracks.algebricks.core.algebra.expressions.BroadcastExpressionAnnotation;
 import org.apache.hyracks.algebricks.core.algebra.expressions.IExpressionAnnotation;
-import org.apache.hyracks.algebricks.core.algebra.expressions.IndexedNLJoinExpressionAnnotation;
 import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
 import org.apache.hyracks.api.exceptions.SourceLocation;
 import org.apache.hyracks.api.exceptions.Warning;
@@ -370,6 +371,19 @@
         });
     }
 
+    private List<String> parseParenthesizedIdentifierList() throws CompilationException {
+        return parseImpl(new ParseFunction<List<String>>() {
+            @Override
+            public  List<String> parse() throws ParseException {
+                return SQLPPParser.this.ParenthesizedIdentifierList();
+            }
+        });
+    }
+
+    private static List<String> parseParenthesizedIdentifierList(String text) throws CompilationException {
+        return new SQLPPParser(text).parseParenthesizedIdentifierList();
+    }
+
     @Override
     public FunctionDecl parseFunctionBody(FunctionSignature signature, List<String> paramNames)
       throws CompilationException {
@@ -566,6 +580,46 @@
         }
     }
 
+    private IExpressionAnnotation parseExpressionAnnotation(Token hintToken) throws SqlppParseException {
+      try {
+        switch (hintToken.hint) {
+          case HASH_BROADCAST_JOIN_HINT:
+            return new BroadcastExpressionAnnotation(BroadcastExpressionAnnotation.BroadcastSide.RIGHT);
+          case INDEXED_NESTED_LOOP_JOIN_HINT:
+            if (hintToken.hintParams == null) {
+              return IndexedNLJoinExpressionAnnotation.INSTANCE_ANY_INDEX;
+            } else {
+              List<String> indexNames = parseParenthesizedIdentifierList(hintToken.hintParams);
+              return IndexedNLJoinExpressionAnnotation.newInstance(indexNames);
+            }
+          case RANGE_HINT:
+            Expression rangeExpr = parseExpression(hintToken.hintParams);
+            RangeMap rangeMap = RangeMapBuilder.parseHint(rangeExpr);
+            return new RangeAnnotation(rangeMap);
+          case SKIP_SECONDARY_INDEX_SEARCH_HINT:
+            if (hintToken.hintParams == null) {
+              return SkipSecondaryIndexSearchExpressionAnnotation.INSTANCE_ANY_INDEX;
+            } else {
+              List<String> indexNames = parseParenthesizedIdentifierList(hintToken.hintParams);
+              return SkipSecondaryIndexSearchExpressionAnnotation.newInstance(indexNames);
+            }
+          case USE_SECONDARY_INDEX_SEARCH_HINT:
+            if (hintToken.hintParams == null) {
+              throw new SqlppParseException(getSourceLocation(hintToken), "Expected index name");
+            } else {
+              List<String> indexNames = parseParenthesizedIdentifierList(hintToken.hintParams);
+              return SecondaryIndexSearchPreferenceAnnotation.newInstance(indexNames);
+            }
+          default:
+            throw new SqlppParseException(getSourceLocation(hintToken), "Unexpected hint " + hintToken.hint);
+        }
+      } catch (CompilationException e) {
+        SqlppParseException pe = new SqlppParseException(getSourceLocation(hintToken), e.getMessage());
+        pe.initCause(e);
+        throw pe;
+      }
+    }
+
     private void ensureNoTypeDeclsInFunction(String fnName, List<Pair<VarIdentifier, TypeExpression>> paramList,
       TypeExpression returnType, Token startToken) throws SqlppParseException {
       for (Pair<VarIdentifier, TypeExpression> p : paramList) {
@@ -2382,8 +2436,10 @@
 {
   // Note: there's a copy of this production in PrimaryExpr() (LOOKAHEAD for FunctionCallExpr())
   //       that copy must be kept in sync with this code
-  prefix = MultipartIdentifierWithHints(SqlppHint.INDEXED_NESTED_LOOP_JOIN_HINT,
-    SqlppHint.SKIP_SECONDARY_INDEX_SEARCH_HINT, SqlppHint.RANGE_HINT)
+  prefix = MultipartIdentifierWithHints(
+    SqlppHint.INDEXED_NESTED_LOOP_JOIN_HINT, SqlppHint.RANGE_HINT,
+    SqlppHint.SKIP_SECONDARY_INDEX_SEARCH_HINT, SqlppHint.USE_SECONDARY_INDEX_SEARCH_HINT
+  )
   (<SHARP> suffix = Identifier())?
   {
     FunctionName result = new FunctionName();
@@ -2445,6 +2501,21 @@
   )
 }
 
+List<String> ParenthesizedIdentifierList() throws ParseException:
+{
+  List<String> list = new ArrayList<String>();
+  String ident = null;
+}
+{
+  <LEFTPAREN>
+  ident = Identifier() { list.add(ident); }
+  ( <COMMA> ident = Identifier() { list.add(ident); } )*
+  <RIGHTPAREN>
+  {
+    return list;
+  }
+}
+
 Pair<Integer, Pair<List<String>, IndexedTypeExpression>> OpenField() throws ParseException:
 {
   IndexedTypeExpression fieldType = null;
@@ -2763,22 +2834,13 @@
     (
       LOOKAHEAD(2)( <LT> | <GT> | <LE> | <GE> | <EQ> | <NE> | <LG> |<SIMILAR> | (<NOT> { not = true; })? <IN>)
         {
-          Token hintToken = fetchHint(token, SqlppHint.INDEXED_NESTED_LOOP_JOIN_HINT,
-            SqlppHint.SKIP_SECONDARY_INDEX_SEARCH_HINT, SqlppHint.HASH_BROADCAST_JOIN_HINT);
+          Token hintToken = fetchHint(token,
+            SqlppHint.HASH_BROADCAST_JOIN_HINT, SqlppHint.INDEXED_NESTED_LOOP_JOIN_HINT,
+            SqlppHint.SKIP_SECONDARY_INDEX_SEARCH_HINT, SqlppHint.USE_SECONDARY_INDEX_SEARCH_HINT
+          );
           if (hintToken != null) {
-            switch (hintToken.hint) {
-              case INDEXED_NESTED_LOOP_JOIN_HINT:
-                annotation = IndexedNLJoinExpressionAnnotation.INSTANCE;
-                break;
-              case SKIP_SECONDARY_INDEX_SEARCH_HINT:
-                annotation = SkipSecondaryIndexSearchExpressionAnnotation.INSTANCE;
-                break;
-              case HASH_BROADCAST_JOIN_HINT:
-                annotation = new BroadcastExpressionAnnotation(BroadcastExpressionAnnotation.BroadcastSide.RIGHT);
-                break;
-            }
+            annotation = parseExpressionAnnotation(hintToken);
           }
-
           String operator = token.image.toLowerCase();
           if (operator.equals("<>")){
               operator = "!=";
@@ -2826,17 +2888,12 @@
       LOOKAHEAD(2)
       (<NOT> { not = true; })? <BETWEEN>
         {
-          Token hintToken = fetchHint(token, SqlppHint.INDEXED_NESTED_LOOP_JOIN_HINT,
-            SqlppHint.SKIP_SECONDARY_INDEX_SEARCH_HINT);
+          Token hintToken = fetchHint(token,
+            SqlppHint.INDEXED_NESTED_LOOP_JOIN_HINT, SqlppHint.SKIP_SECONDARY_INDEX_SEARCH_HINT,
+            SqlppHint.USE_SECONDARY_INDEX_SEARCH_HINT
+          );
           if (hintToken != null) {
-            switch (hintToken.hint) {
-              case INDEXED_NESTED_LOOP_JOIN_HINT:
-                annotation = IndexedNLJoinExpressionAnnotation.INSTANCE;
-                break;
-              case SKIP_SECONDARY_INDEX_SEARCH_HINT:
-                annotation = SkipSecondaryIndexSearchExpressionAnnotation.INSTANCE;
-                break;
-            }
+            annotation = parseExpressionAnnotation(hintToken);
           }
           String operator = token.image.toLowerCase();
           if(not){
@@ -3515,27 +3572,9 @@
     } else {
       CallExpr callExpr = new CallExpr(signature, argList, filterExpr);
       if (funcName.hintToken != null) {
-        switch (funcName.hintToken.hint) {
-          case INDEXED_NESTED_LOOP_JOIN_HINT:
-            callExpr.addHint(IndexedNLJoinExpressionAnnotation.INSTANCE);
-            break;
-          case SKIP_SECONDARY_INDEX_SEARCH_HINT:
-            callExpr.addHint(SkipSecondaryIndexSearchExpressionAnnotation.INSTANCE);
-            break;
-          case RANGE_HINT:
-            try {
-              Expression rangeExpr = parseExpression(funcName.hintToken.hintParams);
-              RangeMap rangeMap = RangeMapBuilder.parseHint(rangeExpr);
-              RangeAnnotation rangeAnn = new RangeAnnotation(rangeMap);
-              callExpr.addHint(rangeAnn);
-            } catch (CompilationException e) {
-              {
-                  SqlppParseException e2 = new SqlppParseException(getSourceLocation(funcName.hintToken), e.getMessage());
-                  e2.initCause(e);
-                  throw e2;
-              }
-            }
-            break;
+        IExpressionAnnotation annotation = parseExpressionAnnotation(funcName.hintToken);
+        if (annotation != null) {
+          callExpr.addHint(annotation);
         }
       }
       FunctionMapUtil.normalizedListInputFunctions(callExpr);