Query 5: Sort the data using a Covering index
Fetch the name and phone number of all
passengers.
SELECT bag.contactPhone, bag.fullName FROM BaggageInfo bag
ORDER BY bag.fullName
Plan:
{
"iterator kind" : "RECEIVE",
"distribution kind" : "ALL_SHARDS",
"order by fields at positions" : [ 1 ],
"input iterator" :
{
"iterator kind" : "SELECT",
"FROM" :
{
"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "compindex_namephone",
"covering index" : true,
"index row variable" : "$$bag_idx",
"index scans" : [
{
"equality conditions" : {},
"range conditions" : {}
}
]
},
"FROM variable" : "$$bag_idx",
"SELECT expressions" : [
{
"field name" : "contactPhone",
"field expression" :
{
"iterator kind" : "FIELD_STEP",
"field name" : "contactPhone",
"input iterator" :
{
"iterator kind" : "VAR_REF",
"variable" : "$$bag_idx"
}
}
},
{
"field name" : "fullName",
"field expression" :
{
"iterator kind" : "FIELD_STEP",
"field name" : "fullName",
"input iterator" :
{
"iterator kind" : "VAR_REF",
"variable" : "$$bag_idx"
}
}
}
]
}
}
Explanation:
- The root iterator of this query plan is a RECEIVE iterator with a single child (input iterator) that is a SELECT iterator. The only property of the RECEIVE iterator in this example is the distribution kind whose value is ALL_SHARDS.
- The results need to be sorted by
fullName
. ThefullName
is part of thecompindex_namephone
index. So in this example, you don't need a separate SORT operator. The sorting is done by the RECEIVE operator using its propertyorder by fields at positions
, which is an array. The value of this array depends on the position of the field which is sorted in the SELECT expression."order by fields at positions" : [ 1 ]
- In this example, the order by is done using the
fullName
which is the second field in the SELECT expression. That is why you see1
in theorder by fields at position
property of the iterator. - The index
compindex_namephone
is used here and in this example, it is a covering index as the query can be evaluated using only the entries of the index. - The index row variable is
$$bag_idx
which is the name of a variable ranging over the index entries produced by the TABLE iterator. Every time a new index entry is produced by the index scan, the$$bag_idx
variable will be bound to that entry. - When the FROM iterator is a TABLE iterator, the FROM variable is the same as
either the index row variable or the row variable of the TABLE iterator,
depending on whether the used index is covering or not. In this example, the
FROM variable is the same as the index row variable
(
$$bag_idx
) as the index is covering. - This index row variable (
$$bag_idx
) will be referenced by iterators implementing the other clauses of the SELECT expression. - In the SELECT expression two fields (
contactPhone,fullName
) are fetched. These correspond to two field names and field expressions in the SELECT expression clause. For both fields, the field expression is computed usingFIELD_STEP
iterator.