This is an automated email from the ASF dual-hosted git repository.
alexpl pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/master by this push:
new 3a2049a79c4 IGNITE-20587 Documentation: SQL hints - Fixes #10983.
3a2049a79c4 is described below
commit 3a2049a79c4ecfb01ee9485e4a3b1b0523ee1067
Author: Vladimir Steshin <[email protected]>
AuthorDate: Fri Oct 13 19:39:34 2023 +0300
IGNITE-20587 Documentation: SQL hints - Fixes #10983.
Signed-off-by: Aleksey Plekhanov <[email protected]>
---
docs/_data/toc.yaml | 2 +
docs/_docs/SQL/sql-calcite.adoc | 160 +++++++++++++++++++--
docs/_docs/sql-reference/hints.adoc | 17 +++
.../query/calcite/hint/HintDefinition.java | 5 +-
4 files changed, 170 insertions(+), 14 deletions(-)
diff --git a/docs/_data/toc.yaml b/docs/_data/toc.yaml
index 4fd8289fcdd..700c9aa9b09 100644
--- a/docs/_data/toc.yaml
+++ b/docs/_data/toc.yaml
@@ -250,6 +250,8 @@
url: sql-reference/system-functions
- title: Data Types
url: sql-reference/data-types
+ - title: Optimizer Hints
+ url: sql-reference/hints
- title: Distributed Computing
items:
- title: Distributed Computing API
diff --git a/docs/_docs/SQL/sql-calcite.adoc b/docs/_docs/SQL/sql-calcite.adoc
index 33456457de7..a35689bc0ea 100644
--- a/docs/_docs/SQL/sql-calcite.adoc
+++ b/docs/_docs/SQL/sql-calcite.adoc
@@ -123,19 +123,6 @@ QUERY_ENGINE=CALCITE
----
--
-=== QUERY_ENGINE Hint
-
-To select a particular engine to run individual queries, use the
`QUERY_ENGINE` hint:
-
-[tabs]
---
-tab:Query with QUERY_ENGINE hint[]
-[source,sql]
-----
-SELECT /*+ QUERY_ENGINE('calcite') */ fld FROM table;
-----
---
-
== SQL Reference
=== DDL
@@ -241,3 +228,150 @@ Below are the data types supported by the Calcite-based
SQL engine:
|`java.lang.Object`
|===
+
+== Optimizer hints [[hints]]
+
+The query optimizer does its best to build the fastest excution plan. However,
this is a far way to create an optimizer
+which is the most effective for each case. You can better know about the data
design, application design or data
+distribution in the cluster. SQL hints can help the optimizer to make
optimizations more rationally or build
+execution plan faster.
+
+[NOTE]
+====
+SQL hints are optional to apply and might be skipped in some cases.
+====
+
+=== Hints format
+SQL hints are defined by a special comment +++/*+ HINT */+++ reffered as a
_hint block_. Spaces before and after the
+hint name are required. The hint block is placed right after a relation
operator, often after _SELECT_. Several hint
+blocks for one relation operator *are not allowed*.
+
+Example:
+[source, SQL]
+----
+SELECT /*+ NO_INDEX */ T1.* FROM TBL1 where T1.V1=? and T1.V2=?
+----
+
+It is allowed to define several hints for the same relation operator. To use
several hints, separate them by comma
+(spaces are optional).
+
+Example:
+[source, SQL]
+----
+SELECT /*+ NO_INDEX, EXPAND_DISTINCT_AGG */ SUM(DISTINCT V1), AVG(DISTINCT V2)
FROM TBL1 GROUP BY V3 WHERE V3=?
+----
+
+==== Hint parameters
+Hint parameters, if required, are placed in brackets after the hint name and
separated by commas.
+
+The hint parameter can be quoted. Quoted parameter is case-sensitive. The
quoted and unquoted parameters cannot be
+defined for the same hint.
+
+Example:
+[source, SQL]
+----
+SELECT /*+ FORCE_INDEX(TBL1_IDX2,TBL2_IDX1) */ T1.V1, T2.V1 FROM TBL1 T1, TBL2
T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;
+
+SELECT /*+ FORCE_INDEX('TBL2_idx1') */ T1.V1, T2.V1 FROM TBL1 T1, TBL2 T2
WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;
+----
+
+=== Hint scope
+Hints are defined for a relation operator, usually for SELECT. Most of the
hints are "visible" to their relation
+operators, for the following operators, queries and subqueries. The hints
defined in the subquery are "visible" only for
+this subquery and its subqueries. Hint is not "visible" to the previous
relation operator if it is defined after it.
+
+Example:
+[source, SQL]
+----
+SELECT /*+ NO_INDEX(TBL1_IDX2), FORCE_INDEX(TBL2_IDX2) */ T1.V1 FROM TBL1 T1
WHERE T1.V2 IN (SELECT T2.V2 FROM TBL2 T2 WHERE T2.V1=? AND T2.V2=?);
+
+SELECT T1.V1 FROM TBL1 T1 WHERE T1.V2 IN (SELECT /*+ FORCE_INDEX(TBL2_IDX2) */
T2.V2 FROM TBL2 T2 WHERE T2.V1=? AND T2.V2=?);
+----
+
+Note that only the first query has a hint in such a case as:
+[source, SQL]
+----
+SELECT /*+ FORCE_INDEX */ V1 FROM TBL1 WHERE V1=? AND V2=?
+UNION ALL
+SELECT V1 FROM TBL1 WHERE V3>?
+----
+
+But *there are exceptions*: hints of engine or optimizer level, such as
link:#hint_disable_rule[_DISABLE_RULE_] or
+link:#hint_query_engine[_QUERY_ENGINE_]. Such hints should be defined at the
beginning of the query and are related to
+the whole query.
+
+=== Hints errors
+The optimizer tries to apply every hint and its parameters, if possible. But
it skips the hint or hint parameter if:
+
+* There is no such supported hint.
+* Required hint parameters are not passed.
+* The hint parameters have been passed, but the hint does not support any
parameter.
+* The hint parameter is incorrect or refers to a nonexistent object, such as a
nonexistent index or table.
+* The current hints or current parameters are incompatible with the previous
ones, such as forcing the use and disabling of the same index.
+
+=== Supportted hints
+
+==== FORCE_INDEX / NO_INDEX
+Forces or disables index scan.
+
+===== Parameters:
+* Empty. To force an index scan for every undelying table. Optimizer will
choose any available index. Or to disable all indexes.
+* Single index name to use or skip exactly this index.
+* Several index names. They can relate to different tables. The optimizer will
choose indexes for scanning or skip them all.
+
+===== Example:
+[source, SQL]
+----
+SELECT /*+ FORCE_INDEX */ T1.* FROM TBL1 T1 WHERE T1.V1 = T2.V1 AND T1.V2 > ?;
+
+SELECT /*+ FORCE_INDEX(TBL1_IDX2, TBL2_IDX1) */ T1.V1, T2.V1 FROM TBL1 T1,
TBL2 T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;
+
+SELECT /*+ NO_INDEX */ T1.* FROM TBL1 T1 WHERE T1.V1 = T2.V1 AND T1.V2 > ?;
+
+SELECT /*+ NO_INDEX(TBL1_IDX2, TBL2_IDX1) */ T1.V1, T2.V1 FROM TBL1 T1, TBL2
T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;
+----
+
+==== ORDERED_JOINS
+Forces join order as appears in a query. Fastens building of joins plan.
+
+===== Example:
+[source, SQL]
+----
+SELECT /*+ ORDERED_JOINS */ T1.V1, T2.V1, T2.V2, T3.V1, T3.V2, T3.V3 FROM TBL1
T1 JOIN TBL2 T2 ON T1.V3=T2.V1 JOIN TBL3 T3 ON T2.V3=T3.V1 AND T2.V2=T3.V2
+
+SELECT t1.v1, t3.v2 FROM TBL1 t1 JOIN TBL3 t3 on t1.v3=t3.v3 WHERE t1.v2 in
(SELECT /*+ ORDERED_JOINS */ t2.v2 FROM TBL2 t2 JOIN TBL3 t3 ON t2.v1=t3.v1)
+----
+
+==== EXPAND_DISTINCT_AGG
+If the optimizer wraps aggregation operations with a join, forces expanding of
only distinct aggregates to the join.
+Removes duplicates before the joining and speeds up it.
+
+===== Example:
+[source, SQL]
+----
+SELECT /*+ EXPAND_DISTINCT_AGG */ SUM(DISTINCT V1), AVG(DISTINCT V2) FROM TBL1
GROUP BY V3
+----
+
+==== QUERY_ENGINE [[hint_query_engine]]
+Selects a particular engine to run individual queries. This is an engine level
hint.
+
+===== Parameters:
+Single parameter required: the engine name.
+
+===== Example:
+[source, SQL]
+----
+SELECT /*+ QUERY_ENGINE('calcite') */ V1 FROM TBL1
+----
+
+==== DISABLE_RULE [[hint_disable_rule]]
+Disables certain optimizer rules. This is an optimizer level hint.
+
+===== Parameters:
+* One or more optimizer rules for skipping.
+
+===== Example:
+[source, SQL]
+----
+SELECT /*+ DISABLE_RULE('MergeJoinConverter') */ T1.* FROM TBL1 T1 JOIN TBL2
T2 ON T1.V1=T2.V1 WHERE T2.V2=?
+----
diff --git a/docs/_docs/sql-reference/hints.adoc
b/docs/_docs/sql-reference/hints.adoc
new file mode 100644
index 00000000000..37ce2b9c994
--- /dev/null
+++ b/docs/_docs/sql-reference/hints.adoc
@@ -0,0 +1,17 @@
+// Licensed to the Apache Software Foundation (ASF) under one or more
+// contributor license agreements. See the NOTICE file distributed with
+// this work for additional information regarding copyright ownership.
+// The ASF licenses this file to You under the Apache License, Version 2.0
+// (the "License"); you may not use this file except in compliance with
+// the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+= Optimizer hints
+
+Currently, optimizer hints are supported only with
link:../SQL/../SQL/sql-calcite.adoc#hints[Calcite SQL engine].
\ No newline at end of file
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintDefinition.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintDefinition.java
index 6ad756d8fb0..748bbc59913 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintDefinition.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/hint/HintDefinition.java
@@ -31,7 +31,10 @@ public enum HintDefinition {
/** Disables planner rules. */
DISABLE_RULE,
- /** Forces expanding of distinct aggregates to join. */
+ /**
+ * If optimizer wraps aggregation operations with a join, forces expanding
of only distinct aggregates to the
+ * join. Removes duplicates before joining and speeds up it.
+ */
EXPAND_DISTINCT_AGG {
/** {@inheritDoc} */
@Override public HintPredicate predicate() {
