Query 8: Using the secondary index with multiple index scans

Fetch the full name and tag number of passengers who are in the given list of names.

SELECT bagdet.fullName, bagdet.bagInfo[].tagNum
FROM BaggageInfo bagdet WHERE bagdet.fullName IN
("Lucinda Beckman", "Adam Phillips",
"Zina Christenson","Fallon Clements");

Plan:

{
  "iterator kind" : "SELECT",
  "FROM" :
  {
     "iterator kind" : "RECEIVE",
     "distribution kind" : "ALL_SHARDS",
     "order by fields at positions" : [ 0 ],
     "input iterator" :
     {
       "iterator kind" : "SELECT",
       "FROM" :
       {
         "iterator kind" : "TABLE",
         "target table" : "BaggageInfo",
         "row variable" : "$$bag",
         "index used" : "fixedschema_conf",
         "covering index" : false,
         "index scans" : [
           {
             "equality conditions" : {},
             "range conditions" : {}
           }
         ]
      },
      "FROM variable" : "$$bag",
      "GROUP BY" : "Grouping by the first expression in the SELECT list",
      "SELECT expressions" : [
         {
           "field name" : "confNo",
           "field expression" :
           {
             "iterator kind" : "FIELD_STEP",
             "field name" : "confNo",
             "input iterator" :
             {
               "iterator kind" : "VAR_REF",
               "variable" : "$$bag"
             }
           }
         },
         {
           "field name" : "TOTAL_BAGS",
           "field expression" :
           {
             "iterator kind" : "FN_COUNT",
             "input iterator" :
             {
               "iterator kind" : "FIELD_STEP",
               "field name" : "bagInfo",
               "input iterator" :
               {
                 "iterator kind" : "VAR_REF",
                 "variable" : "$$bag"
               }
             }
           }
         }
       ]
     }
   },
   "FROM variable" : "$from-1",
   "GROUP BY" : "Grouping by the first expression in the SELECT list",
   "SELECT expressions" : [
     {
       "field name" : "confNo",
       "field expression" :
       {
         "iterator kind" : "FIELD_STEP",
         "field name" : "confNo",
         "input iterator" :
         {
           "iterator kind" : "VAR_REF",
           "variable" : "$from-1"
         }
       }
     },
     {
       "field name" : "TOTAL_BAGS",
       "field expression" :
       {
         "iterator kind" : "FUNC_SUM",
         "input iterator" :
         {
           "iterator kind" : "FIELD_STEP",
           "field name" : "TOTAL_BAGS",
           "input iterator" :
           {
             "iterator kind" : "VAR_REF",
             "variable" : "$from-1"
           }
         }
       }
     }
   ]
}

Explanation:

• The root iterator of this query plan is a RECEIVE iterator with a single child (input iterator) that is aSELECT iterator.

• The value of the FROM field is an iterator; in this case, it is a TABLE iterator.

• The index compindex_namephone is used and the index is not covering ( as you need to scan the table to fetch columns other than the index entries).

• Every value in the IN clause is evaluated using an index scan with an equality condition. There are four index scans that are performed each evaluating one equality condition.

• The FROM variable is the name of a variable ranging over the records produced by the FROM iterator. Here the FROM iterator is a TABLE iterator, and the FROM variable ($$bagdet) is the same as the row variable of the TABLE iterator, as the index used is not covering.

• In the SELECT expression two fields (fullname, bag.bagInfo[].tagNum) are fetched. These correspond to two field names and field expressions in the SELECT expression clause. For the first field, the field expression is computed using FIELD_STEP iterator. For the second field, an ARRAY_CONSTRUCTOR iterator is used which iterates over the corresponding arrays to fetch the field value.