Hints

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 Description
EXPAND VIEWS Expands materialized views. Query rewrite is disabled when this hint is present.
SPARK_SHUFFLE_PARTITIONS 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.
KAFKA_GROUP_ID 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.
FORCE ORDER Specifies that the join order indicated by the query syntax is preserved during query optimization.
OPTIMIZE FOR UNKNOWN Accepted by SQL parser but ignored during query execution.
POOL 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 & management.
RECOMPILE Forces statement recompilation and ignores the cached execution plan if exists.

Table Hints

Query hint name Description
PRIMARY_COPY A query on a cached view will load data from primary cached copy. This is the default behaviour of Querona for cached views.
SECONDARY_COPY A query on a cached view will load data the secondary cached copy.
SKIPCACHE 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
  • MERGE
  • REMOTE

Remarks

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.

Examples

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;