Package org.apache.calcite.rel.hint


package org.apache.calcite.rel.hint
Defines hints interfaces and utilities for relational expressions.

The Syntax

We support the Oracle style hint grammar for both query hint(right after the "SELECT" keyword) and the table hint(right after the table name reference). i.e.
   select /*+ NO_HASH_JOIN, RESOURCE(mem='128mb', parallelism='24') */
   from
     emp /*+ INDEX(idx1, idx2) */
     join
     dept /*+ PROPERTIES(k1='v1', k2='v2') */
     on emp.deptno=dept.deptno
 

Customize Hint Match Rules

Calcite implements a framework to define and propagate the hints. In order to make the hints propagate efficiently, every hint referenced in the sql statement needs to register the match rules for hints propagation.

A match rule is defined though HintPredicate. NodeTypeHintPredicate matches a relational expression by its node type; you can also define a custom instance with more complicated rules, i.e. JOIN with specified relations from the hint options.

Here is the code snippet to illustrate how to config the strategies:

       // Initialize a HintStrategyTable.
       HintStrategyTable strategies = HintStrategyTable.builder()
         .addHintStrategy("time_zone", HintPredicates.SET_VAR)
         .addHintStrategy("index", HintPredicates.TABLE_SCAN)
         .addHintStrategy("resource", HintPredicates.PROJECT)
         .addHintStrategy("use_hash_join",
             HintPredicates.and(HintPredicates.JOIN,
                 HintPredicates.explicit((hint, rel) -> {
                   ...
                 })))
         .hintStrategy("use_merge_join",
             HintStrategyTable.strategyBuilder(
                 HintPredicates.and(HintPredicates.JOIN, joinWithFixedTableName()))
                 .excludedRules(EnumerableRules.ENUMERABLE_JOIN_RULE).build())
         .build();
       // Config the strategies in the config.
       SqlToRelConverter.Config config = SqlToRelConverter.configBuilder()
         .withHintStrategyTable(strategies)
         .build();
       // Use the config to initialize the SqlToRelConverter.
   ...
 

Hints Propagation

There are two cases that need to consider the hints propagation:
  • Right after a SqlNode tree is converted to RelNode tree, we would propagate the hints from the attaching node to its input(children) nodes. The hints are propagated recursively with a RelShuttle, see RelOptUtil#RelHintPropagateShuttle for how it works.
  • During rule planning, in the transforming phrase of a RelOptRule, you should not copy the hints by hand. To ensure correctness, the hints copy work within planner rule is taken care of by Calcite; We make some effort to make the thing easier: right before the new relational expression was registered into the planner, the hints of the old relational expression was copied into the new expression sub-tree(by "new" we mean, the node was created just in the planner rule) if the nodes implement Hintable.

Design Doc

Calcite SQL and Planner Hints Design.