Hint 使用--leading

Oracle hint -- leading 的作用是提示优化器某张表先访问，可以指定一张或多张表，当指定多张表时，表示按指定的顺序访问这几张表。而 Postgresql leading hint的功能与oracle不同，leading 后面必须跟两张或多张表，如果是两张，表示这两张表先进行连接，但两张表的访问顺序不定。如果要严格控制表的访问顺序，还必须使用双括号，具体用法以例子形式进行介绍。

以下的例子在PG 12.3 与 KingbaseES V8R6 进行过验证。 

一、构造数据

?
1
2
3
4
5
6
7
8
9
10
11
create table t1(id1 integer,desc_t1 varchar(400));
create table t2(id2 integer,desc_t2 varchar(400));
create table t3(id3 integer,desc_t3 varchar(400));
 
insert into t1 select generate_series(1,100000),repeat('a',200);
insert into t2 select generate_series(1,100000),repeat('a',200);
insert into t3 select generate_series(1,100000),repeat('a',200);
 
analyze t1;
analyze t2;
analyze t3;

二、Oracle hint -- leading 使用

1、可以只指定一张表

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> select/*+leading(t3) hashjoin(t1 t2 t3)*/ desc_t1,desc_t2,desc_t3 from t1,t2,t3 where id1=id2 and id2=id3;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3350558109
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |   645 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |   645 |     6   (0)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |   430 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T3   |     1 |   215 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |   215 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | T1   |     1 |   215 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

2、指定多张表时，表示访问顺序

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> select/*+leading(t3,t1,t2) hashjoin(t1 t2 t3)*/ desc_t1,desc_t2,desc_t3 from t1,t2,t3 where id1=id2 and id2=id3;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3204703634
 
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |   645 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN            |      |     1 |   645 |     6   (0)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     1 |   430 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T3   |     1 |   215 |     2   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |      |     1 |   215 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | T1   |     1 |   215 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | T2   |     1 |   215 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

三、PG hint -- leading 使用

1、必须至少指定两张表

?
1
2
3
test=# explain analyze select/*+leading(t3) hashjoin(t1 t2 t3)*/ desc_t1,desc_t2,desc_t3 from t1,t2,t3 where id1=id2 and id2=id3;
INFO:  sys_hint_plan: hint syntax error at or near "leading(t3) hashjoin(t1 t2 t3)"
DETAIL:  Leading hint requires at least two relations.

2、leading 只表示连接的顺序  -- 单层括号

以下例子，leading(t3 t1 t2) 表示 t3 t1 先进行连接，结果再与 t2 进行连接。与oracle 不同，这里并没有严格限制访问顺序，实际上还是 t1 最先访问。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
test=# explain analyze select/*+leading(t3 t1 t2) hashjoin(t1 t2 t3)*/ desc_t1,desc_t2,desc_t3 from t1,t2,t3 where id1=id2 and id2=id3;
                                                          QUERY PLAN                                                         
------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=16050.00..44818.00 rows=100000 width=612) (actual time=106.935..352.686 rows=100000 loops=1)
   Hash Cond: (t1.id1 = t2.id2)
   ->  Hash Join  (cost=8025.00..21841.00 rows=100000 width=416) (actual time=54.546..165.037 rows=100000 loops=1)
         Hash Cond: (t1.id1 = t3.id3)
         ->  Seq Scan on t1  (cost=0.00..3942.00 rows=100000 width=208) (actual time=0.006..16.309 rows=100000 loops=1)
         ->  Hash  (cost=3942.00..3942.00 rows=100000 width=208) (actual time=54.457..54.457 rows=100000 loops=1)
               Buckets: 32768  Batches: 8  Memory Usage: 3225kB
               ->  Seq Scan on t3  (cost=0.00..3942.00 rows=100000 width=208) (actual time=0.005..17.343 rows=100000 loops=1)
   ->  Hash  (cost=3942.00..3942.00 rows=100000 width=208) (actual time=52.361..52.362 rows=100000 loops=1)
         Buckets: 32768  Batches: 8  Memory Usage: 3225kB
         ->  Seq Scan on t2  (cost=0.00..3942.00 rows=100000 width=208) (actual time=0.011..16.483 rows=100000 loops=1)
 Planning Time: 0.207 ms
 Execution Time: 357.799 ms
(13 rows)

3、双层括号表示访问顺序

以下例子leadint((t3 t1)) 不仅表示 t3 t1先连接，还指示 t3 表先访问。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
test=# explain analyze select/*+leading((t3 t1)) hashjoin(t1 t2 t3)*/ desc_t1,desc_t2,desc_t3 from t1,t2,t3 where id1=id2 and id2=id3;
                                                          QUERY PLAN                                                         
------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=16050.00..44818.00 rows=100000 width=612) (actual time=103.223..324.500 rows=100000 loops=1)
   Hash Cond: (t1.id1 = t2.id2)
   ->  Hash Join  (cost=8025.00..21841.00 rows=100000 width=416) (actual time=50.143..157.813 rows=100000 loops=1)
         Hash Cond: (t3.id3 = t1.id1)
         ->  Seq Scan on t3  (cost=0.00..3942.00 rows=100000 width=208) (actual time=0.007..16.505 rows=100000 loops=1)
         ->  Hash  (cost=3942.00..3942.00 rows=100000 width=208) (actual time=49.973..49.974 rows=100000 loops=1)
               Buckets: 32768  Batches: 8  Memory Usage: 3225kB
               ->  Seq Scan on t1  (cost=0.00..3942.00 rows=100000 width=208) (actual time=0.006..16.008 rows=100000 loops=1)
   ->  Hash  (cost=3942.00..3942.00 rows=100000 width=208) (actual time=53.019..53.019 rows=100000 loops=1)
         Buckets: 32768  Batches: 8  Memory Usage: 3225kB
         ->  Seq Scan on t2  (cost=0.00..3942.00 rows=100000 width=208) (actual time=0.012..17.379 rows=100000 loops=1)
 Planning Time: 0.150 ms
 Execution Time: 328.360 ms
(13 rows)

　　

posted @   KINGBASE研究院  阅读(1284)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！
点击右上角即可分享
微信分享提示