Query Block Hints
- reference
Query block hints are hints that apply to an entire query block.
A query hint is a type of optimizer hint. Currently SQL++ 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.
To try the examples in this section, set the query context to the inventory scope in the travel sample dataset.
For more information, see Query Context.
Consider the following query, which does not contain an ordering hint.
SELECT a.airportname AS source, r.id AS route, l.name AS airline
FROM airport AS a
JOIN route AS r (1)
ON r.sourceairport = a.faa
JOIN 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 with no hint specified, 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. |
This example is equivalent to the one in the Optimized join ordering example, but includes an ordering hint using simple syntax.
SELECT /*+ ORDERED */
a.airportname AS source, r.id AS route, l.name AS airline
FROM airport AS a
JOIN route AS r (1)
ON r.sourceairport = a.faa
JOIN 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 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. |
This example is equivalent to the one in the Optimized join ordering example, but includes an ordering hint using JSON syntax.
SELECT /*+ {"ordered": true} */
a.airportname AS source, r.id AS route, l.name AS airline
FROM airport AS a
JOIN route AS r (1)
ON r.sourceairport = a.faa
JOIN 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 the joins are ordered just as they were written, just like the query in the previous example.