Examples of queries using index
You can write simple queries to understand how an index is used.
Query 1:
SELECT fullname, ticketNo,bag.bagInfo[].tagNum,
bag.bagInfo[].routing
FROM BaggageInfo bag WHERE 1762340000000 < ticketNo
AND ticketNo < 1762352000000
In the above example, the query contains 2 index predicates. The primary key index is
used as ticketNo
is the primary key here. For the primary key
index, 1762340000000 < ticketNo is a start predicate and ticketNo <
1762352000000 is a stop predicate.
"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "primary index",
"covering index" : false,
"index scans" :
[
{
"equality conditions" : {},
"range conditions" : { "ticketNo" : { "start value" : 1762340000000,
"start inclusive" : false,
"end value" : 1762352000000,
"end inclusive" : false } }
}
]
For more information on how a query is executed, see Query execution plan.
Query 2:
SELECT fullname, ticketNo,bag.bagInfo[].tagNum,
bag.bagInfo[].routing
FROM BaggageInfo bag
WHERE ticketNo > 1762340000000 OR
ticketNo < 1762352000000
In the above example, the query contains 1 index predicate, which is the whole WHERE
expression. The primary key index is used as ticketNo
is the
primary key here. The predicate is a filtering predicate.
"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "primary index",
"covering index" : false,
"index scans" :
[
{
"equality conditions" : {},
"range conditions" : {}
}
],
"index filtering predicate" :
{
"iterator kind" : "OR",
"input iterators" :
[
{
"iterator kind" : "GREATER_THAN",
"left operand" :
{
---
},
"right operand" :
{
---
}
},
{
"iterator kind" : "LESS_THAN",
"left operand" :
{
---
},
"right operand" :
{
---
}
}
]
}
For more information on how a query is executed, see Query execution plan.
Query 3:
SELECT fullName,bag.ticketNo, bag.confNo,
bag.bagInfo[].tagNum,bag.bagInfo[].routing
FROM BaggageInfo bag WHERE bag.confNo="FH7G1W"
In the above example, two indexes are applicable
compindex_tckNoconfNo
and fixedschema_conf
.
fixedschema_conf
is
used as that is a single index on ticketNo
. An index scan is
performed with the equality condition.
"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "fixedschema_conf",
"covering index" : false,
"index scans" :
[
{
"equality conditions" : {"confNo":"FH7G1W"},
"range conditions" : {}
}
]
For more information on how a query is executed, see Query execution plan.
Query 4:
SELECT fullname,bag.bagInfo[].routing FROM BaggageInfo bag
WHERE gender!="F"
In the above example, there is no index predicate, because no index has information about gender.
"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "primary index",
"covering index" : false,
"index scans" :
[
{
"equality conditions" : {},
"range conditions" : {}
}
]
For more information on how a query is executed, see Query execution plan.
Query 5:
SELECT bag.contactPhone, bag.fullName FROM BaggageInfo bag
ORDER BY bag.fullName
In the above example, only the index compindex_namephone
is
applicable. The sort (for the order by clause) will be index-based because the
order-by expression matches the 1st field of the index used by the query. In this
case, the full name and contact phone information needed in the SELECT clause is
available in the index. As a result, the whole query can be answered from the index
only, with no access to the table. So the index compindex_namephone
is a covering index in this example. The query processor will apply this
optimization.
compindex_namephone
is used and it is a covering
index."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" : {}
}
]
For more information on how a query is executed, see Query execution plan.
Query 6:
SELECT fullName, bag.ticketNo, bag.bagInfo[].bagArrivalDate
FROM BaggageInfo bag WHERE EXISTS
bag.bagInfo[$element.bagArrivalDate >="2019-01-01T00:00:00"]
In the above example, the EXISTS condition is actually converted to a filtering predicate. There is one filtering predicate which is the whole WHERE expression.
simpleindex_arrival
is used in this example.
"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "simpleindex_arrival",
"covering index" : false,
"index row variable" : "$$bag_idx",
"index scans" :
[
{
"equality conditions" : {},
"range conditions" : {}
}
],
"index filtering predicate" :
{
"iterator kind" : "GREATER_OR_EQUAL",
"left operand" :
{
---
},
"right operand" :
{
---
}
}
For more information on how a query is executed, see Query execution plan.
Query 7:
SELECT bag.confNo, count(bag.bagInfo) AS TOTAL_BAGS
FROM BaggageInfo bag GROUP BY bag.confNo
In the above example, two indexes fixedschema_conf
and
compindex_tckNoconfNo
are applicable.
fixedschema_conf
is used as that is a single index with only one column
confNo
. For this query, the group-by is index-based. As you need the
entire bagInfo details to determine the number of bags using the aggregate count
function, the index here is not
covering."iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "fixedschema_conf",
"covering index" : false,
"index scans" :
[
{
"equality conditions" : {},
"range conditions" : {}
}
]
For more information on how a query is executed, see Query execution plan.
Query 8:
SELECT bagdet.fullName, bagdet.bagInfo[].tagNum
FROM BaggageInfo bagdet
WHERE bagdet.fullName IN
("Lucinda Beckman", "Adam Phillips",
"Zina Christenson","Fallon Clements")
In the above example, only the index compindex_namephone
is
applicable.
compindex_namephone
is used. An index scan is performed on
compindex_namephone
evaluating four equality predicates.
"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bagdet",
"index used" : "compindex_namephone",
"covering index" : false,
"index scans" :
[
{
"equality conditions" : {"fullName":"Lucinda Beckman"},
"range conditions" : {}
},
{
"equality conditions" : {"fullName":"Adam Phillips"},
"range conditions" : {}
},
{
"equality conditions" : {"fullName":"Zina Christenson"},
"range conditions" : {}
},
{
"equality conditions" : {"fullName":"Fallon Clements"},
"range conditions" : {}
}
]
For more information on how a query is executed, see Query execution plan.
Query 9:
SELECT fullName,bag.ticketNo, bag.confNo,
bag.bagInfo[].tagNum,bag.bagInfo[].routing
FROM BaggageInfo bag WHERE
bag.ticketNo=1762311547917
AND bag.confNo="FH7G1W"
In the above example, though the index
compindex_tckNoconfNo
is available, only the primary index (for
ticketNo
) gets used. An index scan is performed on the primary index and
the WHERE expression is evaluated.
"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "primary index",
"covering index" : false,
"index scans" :
[
{
"equality conditions" : {"ticketNo":1762311547917},
"range conditions" : {}
}
]
For more information on how a query is executed, see Query execution plan.
Query 10:
SELECT $flt_src as SOURCE, count(*) as COUNT
FROM BaggageInfo $bag,
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src
GROUP BY $flt_src
In the above example, there is no index on the fltRouteSrc
field. So
the grouping is done in a generic way. An internal variable is created that iterates
over the records produced by the SELECT statement.
"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$bag",
"index used" : "primary index",
"covering index" : false,
"index scans" :
[
{
"equality conditions" : {},
"range conditions" : {}
}
]
For more information on how a query is executed, see Query execution plan.