Previous Next JavaScript must be enabled to correctly display this content

## Query 9: A SINGLE PARTITION query using a primary index

Select the ticket details (ticket number, reservation code, tag number, and routing) for a passenger with a specific ticket number and reservation code.

    SELECT fullName,bag.ticketNo, bag.confNo, bag.bagInfo[].tagNum,
    bag.bagInfo[].routing FROM BaggageInfo bag WHERE
    bag.ticketNo=1762311547917 AND bag.confNo="FH7G1W"

Plan:

    {
      "iterator kind" : "RECEIVE",
      "distribution kind" : "SINGLE_PARTITION",
      "input iterator" :
      {
        "iterator kind" : "SELECT",
        "FROM" :
        {
          "iterator kind" : "TABLE",
          "target table" : "BaggageInfo",
          "row variable" : "$$bag",
          "index used" : "primary index",
          "covering index" : false,
          "index scans" : [
            {
              "equality conditions" : {"ticketNo":1762311547917},
              "range conditions" : {}
            }
          ]
        },
        "FROM variable" : "$$bag",
        "WHERE" :
        {
          "iterator kind" : "EQUAL",
          "left operand" :
          {
            "iterator kind" : "FIELD_STEP",
            "field name" : "confNo",
            "input iterator" :
            {
              "iterator kind" : "VAR_REF",
              "variable" : "$$bag"
            }
          },
          "right operand" :
          {
            "iterator kind" : "CONST",
            "value" : "FH7G1W"
          }
        },
        "SELECT expressions" : [
          {
            "field name" : "fullName",
            "field expression" :
            {
              "iterator kind" : "FIELD_STEP",
              "field name" : "fullName",
              "input iterator" :
              {
                "iterator kind" : "VAR_REF",
                "variable" : "$$bag"
              }
            }
          },
          {
            "field name" : "ticketNo",
            "field expression" :
            {
              "iterator kind" : "FIELD_STEP",
              "field name" : "ticketNo",
              "input iterator" :
              {
                "iterator kind" : "VAR_REF",
                "variable" : "$$bag"
              }
            }
          },
          {
            "field name" : "confNo",
            "field expression" :
            {
              "iterator kind" : "FIELD_STEP",
              "field name" : "confNo",
              "input iterator" :
              {
                "iterator kind" : "VAR_REF",
                "variable" : "$$bag"
              }
            }
          },
          {
            "field name" : "tagNum",
            "field expression" :
            {
              "iterator kind" : "ARRAY_CONSTRUCTOR",
              "conditional" : true,
              "input iterators" : [
                {
                  "iterator kind" : "FIELD_STEP",
                  "field name" : "tagNum",
                  "input iterator" :
                  {
                    "iterator kind" : "ARRAY_FILTER",
                    "input iterator" :
                    {
                      "iterator kind" : "FIELD_STEP",
                      "field name" : "bagInfo",
                      "input iterator" :
                      {
                        "iterator kind" : "VAR_REF",
                        "variable" : "$$bag"
                      }
                    }
                  }
                }
              ]
            }
          },
          {
            "field name" : "routing",
            "field expression" :
            {
              "iterator kind" : "ARRAY_CONSTRUCTOR",
              "conditional" : true,
              "input iterators" : [
                {
                  "iterator kind" : "FIELD_STEP",
                  "field name" : "routing",
                  "input iterator" :
                  {
                    "iterator kind" : "ARRAY_FILTER",
                    "input iterator" :
                    {
                      "iterator kind" : "FIELD_STEP",
                      "field name" : "bagInfo",
                      "input iterator" :
                      {
                        "iterator kind" : "VAR_REF",
                        "variable" : "$$bag"
                      }
                    }
                  }
                }
              ]
            }
          }
        ]
      }
    }

Explanation:

- The root iterator of this query plan is a RECEIVE iterator with a single child (input iterator) that is a SELECT iterator.
- This query specifies a complete shard key in its WHERE clause. As a result, its full result set is contained in a single partition, and the RECEIVE iterator will send its subplan to a single RN that stores that partition.
- The value of the `FROM` field is an iterator; in this case, it is a TABLE iterator.
- A SINGLE_PARTITION query can reference a primary index or a secondary index. The primary key index is used in this example. The index is not covering ( as you need to scan the table to fetch columns other than the index entries).
- The index scan property contains the start and stop conditions that define the index scans to be performed.
- 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 (`$$bag`) is the same as the row variable of the TABLE iterator, as the index used is not covering.
- In the SELECT expression five fields (`fullname, ticketNo,confNo, bag.bagInfo[].tagNum,bag.bagInfo[].routing`) are fetched. These correspond to five field names and field expressions in the SELECT expression clause. For the first three fields, the field expression is computed using `FIELD_STEP` iterator. For the last 2 fields, an `ARRAY_CONSTRUCTOR` iterator is used which iterates over the corresponding arrays to fetch the field value.