Hint 使用--leading
Oracle hint -- leading 的作用是提示优化器某张表先访问，可以指定一张或多张表，当指定多张表时，表示按指定的顺序访问这几张表。而 Postgresql leading hint的功能与oracle不同，leading 后面必须跟两张或多张表，如果是两张，表示这两张表先进行连接，但两张表的访问顺序不定。如果要严格控制表的访问顺序，还必须使用双括号，具体用法以例子形式进行介绍。
以下的例子在PG 12.3 与 KingbaseES V8R6 进行过验证。
一、构造数据
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、可以只指定一张表
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、指定多张表时，表示访问顺序
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、必须至少指定两张表
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 最先访问。
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 表先访问。
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)
KINGBASE研究院
