Previous Next JavaScript must be enabled to correctly display this content

## 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 a SELECT 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.