Query Block Hints
- reference
- Couchbase Server 7.1
Query block hints are hints that apply to an entire query block.
A query hint is a type of optimizer hint. Currently N1QL supports only one query block hint: ORDERED.
There are two possible formats for each optimizer hint: simple syntax and JSON syntax. Note that you cannot mix simple syntax and JSON syntax in the same hint comment.
ORDERED
If present, this hint directs the optimizer to order any joins just as they are ordered in the query. If not specified, the optimizer determines the optimal join order.
Simple Syntax
ordered-hint-simple ::= 'ORDERED'
With the simple syntax, this hint takes no arguments. You may only use this hint once within the hint comment.
JSON Syntax
ordered-hint-json ::= '"ordered"' ':' 'true'
With the JSON syntax, this hint takes the form of an ordered property.
You may only use this property once within the hint comment.
The value of this property must be set to true.
Examples
For the examples in this section, it is assumed that the cost-based optimizer is active, and all optimizer statistics are up-to-date.
SELECT /*+ ORDERED */
a.airportname AS source, r.id AS route, l.name AS airline
FROM `travel-sample`.inventory.airport AS a
JOIN `travel-sample`.inventory.route AS r (1)
ON r.sourceairport = a.faa
JOIN `travel-sample`.inventory.airline AS l (2)
ON r.airlineid = META(l).id
WHERE l.name = "40-Mile Air";| 1 | Join the airport keyspace to the route keyspace. |
| 2 | Join the resulting dataset to the airline keyspace. |
SELECT /*+ {"ordered": true} */
a.airportname AS source, r.id AS route, l.name AS airline
FROM `travel-sample`.inventory.airport AS a
JOIN `travel-sample`.inventory.route AS r (1)
ON r.sourceairport = a.faa
JOIN `travel-sample`.inventory.airline AS l (2)
ON r.airlineid = META(l).id
WHERE l.name = "40-Mile Air";| 1 | Join the airport keyspace to the route keyspace. |
| 2 | Join the resulting dataset to the airline keyspace. |
If you examine the plan for this query, or the query in the previous example, you can see that the joins are ordered just as they were written.
| 1 | Join the airport keyspace to the route keyspace. |
| 2 | Join the resulting dataset to the airline keyspace. |
For further insight into join enumeration, consider the following query, which is equivalent to the previous two examples, but without the ORDERED hint.
SELECT a.airportname AS source, r.id AS route, l.name AS airline
FROM `travel-sample`.inventory.airport AS a
JOIN `travel-sample`.inventory.route AS r (1)
ON r.sourceairport = a.faa
JOIN `travel-sample`.inventory.airline AS l (2)
ON r.airlineid = META(l).id
WHERE l.name = "40-Mile Air";| 1 | Join the airport keyspace to the route keyspace. |
| 2 | Join the resulting dataset to the airline keyspace. |
If you examine the plan for this query, you can see that because there is no hint, the optimizer has re-ordered the joins.
| 1 | Join the airline keyspace to the route keyspace. |
| 2 | Join the resulting dataset to the airport keyspace. |