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 usingFIELD_STEP
iterator. For the last 2 fields, anARRAY_CONSTRUCTOR
iterator is used which iterates over the corresponding arrays to fetch the field value.