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 Managing Apache Spark.
RECOMPILE	Forces statement recompilation and ignores the cached execution plan if exists.

Table Hints#

Table hint name	Description
PRIMARY_COPY	Querying a materialized view will load data from the primary cached copy. This is the default behaviour of Querona for materialized views.
SECONDARY_COPY	Querying a materialized view will load data from the secondary cached copy.
SKIPCACHE	Querying a materialized view with a SKIPCACHE hint ignores the materialization of the view and uses data from objects lower in the dependency hierachy. For example, consider the following dependency hierarchy: materialized view vTop depends on the materialized view vMiddle, which depends on the base table tDown. Normally, when you query vTop, data comes from the store holding the materialized data. If you use the SKIPCACHE hint on vTop, data comes from the vMiddle.

Join Hints#

The following join hints are accepted by the SQL parser but are 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 objects (table, view, tabular function, etc.). If a join hint is specified for any two objects, the query optimizer automatically enforces the join order for all joined objects 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;

Hints

Contents