[CALCITE-482] Implement SQL and planner hints

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.21.0
• Fix Version/s: 1.22.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Danny Chen
• Reporter: Vladimir Sitnikov
• Created: 26/Nov/14 17:54
• Updated: 10/Jun/22 10:17
• Resolved: 12/Nov/19 02:24

Description

Query optimizer can never be perfect, so it makes sense to implement hints to allow user better control the execution.

For instance: "never merge this subquery with others" (`/+ no_merge/`), "treat those tables as leading ones" (`/*+ leading */`), etc.
Hints would enable predictable performance and the planning time would be improved as well.

Issue Links

• duplicates: CALCITE-495 Support hints to be passed through select/DML commands
• is related to: CALCITE-299 Divide the join graph into partitions that can be optimized separately
• is related to: CALCITE-481 Add "Spool" operator, to allow re-use of relational expressions
• is related to: CALCITE-5188 Review (and improve?) hints
• relates to: CALCITE-3519 `inheritPath` of `RelHint` represented by `ImmutableBitSet` may be incomplete.
• relates to: CALCITE-3940 Hint item can not parse correctly if the name is right after token "/*+"
• relates to: CALCITE-4899 Support hint for lateral table syntax
• relates to: CALCITE-3518 Use List instead of BitSet to keep inheritPath in RelHint
• links to: GitHub Pull Request #1354
• links to: Implement SQL and Planner Hints In Calcite

Sub-Tasks

• Strengthen the java doc and code of class RelHint and SqlHint: Closed
• Enrich tests for SQL hints in SqlHintsConverterTest: Closed
• Propagate hints when decorrelating a query: Closed
• Support SQL hints for Aggregate: Closed
• Support hot Groupby keys hint: Closed
• Support SQL hints for Calc: Closed
• Support Hint for Sort: Open
• Reduce ambiguous hint strategies: Closed
• Supports hint option as string or numeric literal: Closed
• Hints should be propagated correctly in planner rules if original node is transformed to different kind: Closed
• Add an interface to pass the table hints to RelOptTable: Closed
• Add hint option checker to customize the option validation: Closed
• Following the change to add hints to RelNode, deprecate the old constructors: Closed
• Add hints to RelBuilder: Closed
• Add an interface in RelOptRuleCall to customize the propagation of hints before registering into planner rule: Closed
• Add a Builder to RelHint: Closed
• HintStrategy can specify excluded rules for planner: Closed
• Refactor HintStrategy to HintPredicate: Closed
• Support hint option key as string literal: Closed