Hints are options or strategies specified for enforcement by the query processor on statements. The hints override any execution plan the query optimizer might select for a query.

The following hints are described in this section:

Query Hints#

Query hint name



Expands materialized views. Query rewrite is disabled when this hint is present.


If a query is executed on Apache Spark, it forces the executor to use the designated shuffle partition count. Usage: SPARK_SHUFFLE_PARTITIONS integerValue, where integerValue is a number of partitions to use.


If a query is executed on Apache Kafka, it forces Querona to the query to register itself to a specified Kafka group. Usage: KAFKA_GROUP_ID ‘stringValue’, where ‘stringValue’ is a Kafka group id name Consumers label themselves with a consumer group name, and each record published to a topic is delivered to one consumer instance within each subscribing consumer group. Consumer instances can be in separate processes or on separate machines. Fore more information navigate to Kafka documentation.


Specifies that the join order indicated by the query syntax is preserved during query optimization.


Accepted by SQL parser but ignored during query execution.


If a query is executed on Apache Spark, it forces Spark to use a designated scheduler pool for query execution. Usage: POOL ‘stringValue’, where ‘stringValue’ is a name of the scheduler pool in Apache Spark. For more information see the “Scheduler pool” chapter Spark connection and management.


Forces statement recompilation and ignores the cached execution plan if exists.

Table Hints#

Query hint name



A query on a cached view will load data from primary cached copy. This is the default behaviour of Querona for cached views.


A query on a cached view will load data the secondary cached copy.


A select with SKIPCACHE hint ignores all caches and loads data directly from the data source.

Join Hints#

The following join hints are accepted by SQL parser but ignored during query execution:

  • LOOP

  • HASH




Join hints are specified in the FROM clause of a query. Join hints enforce a join strategy between two tables. If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query, based on the position of the ON keywords. When a CROSS JOIN is used without the ON clause, parentheses can be used to indicate the join order.


Using HASH#

The following example specifies that the JOIN operation in the query is performed by a HASH join. The example uses the AdventureWorks2012 database.

SELECT p.Name, pr.ProductReviewID
  FROM Production.Product AS p
  LEFT OUTER HASH JOIN Production.ProductReview AS pr
    ON p.ProductID = pr.ProductID
 ORDER BY ProductReviewID DESC;