xzj7019 commented on code in PR #1597:
URL: https://github.com/apache/doris-website/pull/1597#discussion_r1897212992
##########
docs/query-acceleration/hints/leading-hint.md:
##########
@@ -0,0 +1,505 @@
+---
+{
+ "title": "Leading Hint",
+ "language": "en"
+}
+---
+
+<!--
+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.
+-->
+
+Leading Hint is a powerful query optimization technique that allows users to
guide the Doris optimizer to determine the table join order in the query plan.
Correct use of Leading Hint can significantly improve the performance of
complex queries. This article will describe in detail how to use Leading Hint
to control the join order in Doris.
+
+## Regular Leading Hint
+
+### Syntax
+
+Leading Hint allows specifying the table join order that the optimizer should
follow. In Doris, the basic syntax of Leading Hint is as follows:
+
+```sql
+SELECT /*+ LEADING(tablespec [tablespec]...) */ ...
+```
+
+It should be noted that:
+
+- Leading Hint is surrounded by `/*+` and `*/` and placed after the SELECT
keyword in the SQL statement.
+- `tablespec` is the table name or table alias, and at least two tables need
to be specified.
+- Multiple tables are separated by spaces or ','.
+- You can use curly braces `{}` to explicitly specify the shape of the Join
Tree.
+
+For example:
+
+```sql
+mysql> explain shape plan select /*+ leading(t2 t1) */ * from t1 join t2 on c1
= c2;
++------------------------------------------------------------------------------+
+| Explain String(Nereids Planner)
|
++------------------------------------------------------------------------------+
+| PhysicalResultSink
|
+| --PhysicalDistribute[DistributionSpecGather]
|
+| ----PhysicalProject
|
+| ------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2)) otherCondition=()
|
+| --------PhysicalOlapScan[t2]
|
+| --------PhysicalDistribute[DistributionSpecHash]
|
+| ----------PhysicalOlapScan[t1]
|
+|
|
+| Hint log:
|
+| Used: leading(t2 t1)
|
+| UnUsed:
|
+| SyntaxError:
|
++------------------------------------------------------------------------------+
+```
+
+When Leading Hint is not effective, the normal process will be used to
generate the plan. EXPLAIN will display whether the used Hint is effective,
mainly divided into three types:
+
+ - `Used`：Leading Hint is effective normally.
+ - `Unused`：The unsupported cases here include that the join order specified
by Leading Hint is not equivalent to the original SQL or the feature is not
supported in this version (see limitations for details).
+ - `SyntaxError`： Indicates a syntax error in Leading Hint, such as the
inability to find the corresponding table.
+
+1. The default syntax of Leading Hint constructs a left-deep tree:
+```sql
+mysql> explain shape plan select /*+ leading(t1 t2 t3) */ * from t1 join t2 on
c1 = c2 join t3 on c2=c3;
++--------------------------------------------------------------------------------+
+| Explain String(Nereids Planner)
|
++--------------------------------------------------------------------------------+
+| PhysicalResultSink
|
+| --PhysicalDistribute[DistributionSpecGather]
|
+| ----PhysicalProject
|
+| ------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3)) otherCondition=()
|
+| --------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=() |
+| ----------PhysicalOlapScan[t1]
|
+| ----------PhysicalDistribute[DistributionSpecHash]
|
+| ------------PhysicalOlapScan[t2]
|
+| --------PhysicalDistribute[DistributionSpecHash]
|
+| ----------PhysicalOlapScan[t3]
|
+|
|
+| Hint log:
|
+| Used: leading(t1 t2 t3)
|
+| UnUsed:
|
+| SyntaxError:
|
++--------------------------------------------------------------------------------+
+```
+
+2. At the same time, curly braces can be used to specify the shape of the Join
tree:
+```sql
+mysql> explain shape plan select /*+ leading(t1 {t2 t3}) */ * from t1 join t2
on c1 = c2 join t3 on c2=c3;
++----------------------------------------------------------------------------------+
+| Explain String(Nereids Planner)
|
++----------------------------------------------------------------------------------+
+| PhysicalResultSink
|
+| --PhysicalDistribute[DistributionSpecGather]
|
+| ----PhysicalProject
|
+| ------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2)) otherCondition=()
|
+| --------PhysicalOlapScan[t1]
|
+| --------PhysicalDistribute[DistributionSpecHash]
|
+| ----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=() |
+| ------------PhysicalOlapScan[t2]
|
+| ------------PhysicalDistribute[DistributionSpecHash]
|
+| --------------PhysicalOlapScan[t3]
|
+|
|
+| Hint log:
|
+| Used: leading(t1 { t2 t3 })
|
+| UnUsed:
|
+| SyntaxError:
|
++----------------------------------------------------------------------------------+
+```
+
+3. When a View is used as an alias to participate in JoinReorder, the
corresponding View can be specified as a parameter of Leading Hint. For example:
+
+```sql
+mysql> explain shape plan select /*+ leading(alias t1) */ count(*) from t1
join (select c2 from t2 join t3 on t2.c2 = t3.c3) as alias on t1.c1 = alias.c2;
+
+--------------------------------------------------------------------------------------+
+ | Explain String(Nereids Planner)
|
+
+--------------------------------------------------------------------------------------+
+ | PhysicalResultSink
|
+ | --hashAgg[GLOBAL]
|
+ | ----PhysicalDistribute[DistributionSpecGather]
|
+ | ------hashAgg[LOCAL]
|
+ | --------PhysicalProject
|
+ | ----------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = alias.c2))
otherCondition=() |
+ | ------------PhysicalProject
|
+ | --------------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=() |
+ | ----------------PhysicalProject
|
+ | ------------------PhysicalOlapScan[t2]
|
+ | ----------------PhysicalDistribute[DistributionSpecHash]
|
+ | ------------------PhysicalProject
|
+ | --------------------PhysicalOlapScan[t3]
|
+ | ------------PhysicalDistribute[DistributionSpecHash]
|
+ | --------------PhysicalProject
|
+ | ----------------PhysicalOlapScan[t1]
|
+ |
|
+ | Hint log:
|
+ | Used: leading(alias t1)
|
+ | UnUsed:
|
+ | SyntaxError:
|
+
+--------------------------------------------------------------------------------------+
+```
+
+### Case
+
+#### Basic Scenario
+
+The table creation statements are as follows:
+
+```sql
+CREATE DATABASE testleading;
+USE testleading;
+
+create table t1 (c1 int, c11 int) distributed by hash(c1) buckets 3
properties('replication_num' = '1');
+create table t2 (c2 int, c22 int) distributed by hash(c2) buckets 3
properties('replication_num' = '1');
+create table t3 (c3 int, c33 int) distributed by hash(c3) buckets 3
properties('replication_num' = '1');
+create table t4 (c4 int, c44 int) distributed by hash(c4) buckets 3
properties('replication_num' = '1');
+```
+
+The original plan:
+
+```sql
+mysql> explain shape plan select * from t1 join t2 on t1.c1 = c2;
++-------------------------------------------+
+| Explain String |
++-------------------------------------------+
+| PhysicalResultSink |
+| --PhysicalDistribute |
+| ----PhysicalProject |
+| ------hashJoin[INNER_JOIN](t1.c1 = t2.c2) |
+| --------PhysicalOlapScan[t2] |
+| --------PhysicalDistribute |
+| ----------PhysicalOlapScan[t1] |
++-------------------------------------------+
+```
+
+When we need to exchange the join order of t1 and t2, we only need to add
`leading(t2 t1)` in front. When executing `explain`, it will show whether this
hint is used. The following is the Leading plan: `Used` indicates that the Hint
is effective normally.
+
+```sql
+mysql> explain shape plan select /*+ leading(t2 t1) */ * from t1 join t2 on c1
= c2;
++------------------------------------------------------------------------------+
+| Explain String(Nereids Planner)
|
++------------------------------------------------------------------------------+
+| PhysicalResultSink
|
+| --PhysicalDistribute[DistributionSpecGather]
|
+| ----PhysicalProject
|
+| ------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2)) otherCondition=()
|
+| --------PhysicalOlapScan[t2]
|
+| --------PhysicalDistribute[DistributionSpecHash]
|
+| ----------PhysicalOlapScan[t1]
|
+|
|
+| Hint log:
|
+| Used: leading(t2 t1)
|
+| UnUsed:
|
+| SyntaxError:
|
++------------------------------------------------------------------------------+
+```
+
+If there is a syntax error in Leading Hint, when executing `explain`, the
corresponding information will be displayed in `SyntaxError`, but the plan can
still be generated normally, just without using Leading. For example:
+
+```sql
+mysql> explain shape plan select /*+ leading(t2 t3) */ * from t1 join t2 on
t1.c1 = c2;
++--------------------------------------------------------+
+| Explain String |
++--------------------------------------------------------+
+| PhysicalResultSink |
+| --PhysicalDistribute |
+| ----PhysicalProject |
+| ------hashJoin[INNER_JOIN](t1.c1 = t2.c2) |
+| --------PhysicalOlapScan[t1] |
+| --------PhysicalDistribute |
+| ----------PhysicalOlapScan[t2] |
+| |
+| Used: |
+| UnUsed: |
+| SyntaxError: leading(t2 t3) Msg:can not find table: t3 |
++--------------------------------------------------------+
+```
+
+#### Extended Scenario
+
+1. Left-Deep Tree
+
+As mentioned above, when the query statement in Doris does not use any
parentheses, Leading will default to generating a left-deep tree.
+
+```sql
+mysql> explain shape plan select /*+ leading(t1 t2 t3) */ * from t1 join t2 on
t1.c1 = c2 join t3 on c2 = c3;
++--------------------------------------------------------------------------------+
+| Explain String(Nereids Planner)
|
++--------------------------------------------------------------------------------+
+| PhysicalResultSink
|
+| --PhysicalDistribute[DistributionSpecGather]
|
+| ----PhysicalProject
|
+| ------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3)) otherCondition=()
|
+| --------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=() |
+| ----------PhysicalOlapScan[t1]
|
+| ----------PhysicalDistribute[DistributionSpecHash]
|
+| ------------PhysicalOlapScan[t2]
|
+| --------PhysicalDistribute[DistributionSpecHash]
|
+| ----------PhysicalOlapScan[t3]
|
+|
|
+| Hint log:
|
+| Used: leading(t1 t2 t3)
|
+| UnUsed:
|
+| SyntaxError:
|
++--------------------------------------------------------------------------------+
+```
+
+2. Right-Deep Tree
+
+When you need to make the shape of the plan a right-deep tree, Bushy tree, or
zig-zag tree, you only need to add curly braces to limit the shape of the plan,
without the need to use swap like Oracle to adjust step by step from the
left-deep tree.
+
+```sql
+mysql> explain shape plan select /*+ leading(t1 {t2 t3}) */ * from t1 join t2
on t1.c1 = c2 join t3 on c2 = c3;
++-----------------------------------------------+
+| Explain String |
++-----------------------------------------------+
+| PhysicalResultSink |
+| --PhysicalDistribute |
+| ----PhysicalProject |
+| ------hashJoin[INNER_JOIN](t1.c1 = t2.c2) |
+| --------PhysicalOlapScan[t1] |
+| --------PhysicalDistribute |
+| ----------hashJoin[INNER_JOIN](t2.c2 = t3.c3) |
+| ------------PhysicalOlapScan[t2] |
+| ------------PhysicalDistribute |
+| --------------PhysicalOlapScan[t3] |
+| |
+| Used: leading(t1 { t2 t3 }) |
+| UnUsed: |
+| SyntaxError: |
++-----------------------------------------------+
+```
+
+3. Bushy Tree
+
+```sql
+mysql> explain shape plan select /*+ leading({t1 t2} {t3 t4}) */ * from t1
join t2 on t1.c1 = c2 join t3 on c2 = c3 join t4 on c3 = c4;
++-----------------------------------------------+
+| Explain String |
++-----------------------------------------------+
+| PhysicalResultSink |
+| --PhysicalDistribute |
+| ----PhysicalProject |
+| ------hashJoin[INNER_JOIN](t2.c2 = t3.c3) |
+| --------hashJoin[INNER_JOIN](t1.c1 = t2.c2) |
+| ----------PhysicalOlapScan[t1] |
+| ----------PhysicalDistribute |
+| ------------PhysicalOlapScan[t2] |
+| --------PhysicalDistribute |
+| ----------hashJoin[INNER_JOIN](t3.c3 = t4.c4) |
+| ------------PhysicalOlapScan[t3] |
+| ------------PhysicalDistribute |
+| --------------PhysicalOlapScan[t4] |
+| |
+| Used: leading({ t1 t2 } { t3 t4 }) |
+| UnUsed: |
+| SyntaxError: |
++-----------------------------------------------+
+```
+
+4. zig-zag 树
Review Comment:
tree
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
