Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

A newer version of this documentation is available.

View Latest

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'
    Syntax diagram: refer to source code listing

    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'
    Syntax diagram: refer to source code listing

    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.

    ORDERED hint — simple syntax
    Query
    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.
    ORDERED hint — JSON syntax
    Query
    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.

    Query plan with ORDERED hint
    1 Join the airport keyspace to the route keyspace.
    2 Join the resulting dataset to the airline keyspace.
    Optimized join ordering

    For further insight into join enumeration, consider the following query, which is equivalent to the previous two examples, but without the ORDERED hint.

    Query
    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.

    Query plan with optimized join order
    1 Join the airline keyspace to the route keyspace.
    2 Join the resulting dataset to the airport keyspace.

    Legacy Equivalent

    There is no legacy clause equivalent to this hint.