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