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 Lyftrondata 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 Lyftrondata 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;