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;