多表hint¶
use_nl (A , B)¶
use_nl ::=
- 如果A、B都是基表，则表示以A作为外表，B作为内表，两者做nestloop连接。
- 如果A是表集合，B是基表，定义A=（table1, table2, ... ,table n）。此时的语义为：以集合A内各表做连接得到的联接表作为外表，B作为内表，两者做nestloop连接。（当B是表集合、A是基表时情况类似）
- 如果A、B均是表集合，则表示以集合A内各表做连接得到的联接表作为外表，以集合B内各表做连接得到的联接表作为内表，两者做nestloop连接。
举例:
-- 清理环境
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
-- 创建表
CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);
CREATE TABLE c(c1 INT, c2 INT);
explain select /*+ use_nl(c,(a,b))*/ * from a, b, c
where a.a1=b.b1 and a.a2=c.c2;
QUERY PLAN(text)
----------------------
Nested Loop(Inner Join) (cost=8293.46..75282600.44 rows=13743895 width=24)
Join Filter: ("inner".A2 = "outer".C2)
-> Seq Scan on C (cost=0.00..89.92 rows=8192 width=8)
-> Materialize (cost=8293.46..13287.91 rows=335544 width=16)
-> Merge Join(Inner Join) (cost=1244.80..6318.92 rows=335544 width=16)
Merge Cond: ("outer".A1 = "inner".B1)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: A1
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: B1
-> Seq Scan on B (cost=0.00..89.92 rows=8192 width=8)
总数目：12
-- 删除表
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
use_hj (A , B)¶
use_hj ::=
- 如果A、B都是基表，则表示以A作为外表，B作为内表，两者做hash连接。
- 如果A是表集合，B是基表，定义A=（table1, table2, ... ,table n）。此时的语义为：以集合A内各表做连接得到的联接表作为外表，B作为内表，两者做hash连接。（当B是表集合、A是基表时情况类似）
- 如果A、B均是表集合，则表示以集合A内各表做连接得到的联接表作为外表，以集合B内各表做连接得到的联接表作为内表，两者做hash连接。
举例:
-- 清理环境
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
DROP TABLE d CASCADE;
-- 创建表
CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);
CREATE TABLE c(c1 INT, c2 INT);
CREATE TABLE d(d1 INT, d2 INT);
explain select /*+ use_hj((b,d),(a,c)) */ * from a, b, c, d
where a.a1=b.b1 and a.a2=c.c2 and b.b2=d.d2;
QUERY PLAN(text)
----------------------
Hash Join(Inner Join) (cost=13397.02..19727713.61 rows=562949953 width=32)
Hash Cond: ("outer".B1 = "inner".A1)
-> Merge Join(Inner Join) (cost=1244.80..6318.92 rows=335544 width=16)
Merge Cond: ("outer".B2 = "inner".D2)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: B2
-> Seq Scan on B (cost=0.00..89.92 rows=8192 width=8)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: D2
-> Seq Scan on D (cost=0.00..89.92 rows=8192 width=8)
-> Hash (cost=6318.92..6318.92 rows=335544 width=16)
-> Merge Join(Inner Join) (cost=1244.80..6318.92 rows=335544 width=16)
Merge Cond: ("outer".A2 = "inner".C2)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: A2
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: C2
-> Seq Scan on C (cost=0.00..89.92 rows=8192 width=8)
总数目：19
-- 删除表
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
DROP TABLE d CASCADE;
use_mj (A , B)¶
use_mj ::=
- 如果A、B都是基表，则表示以A作为外表，B作为内表，两者做merge连接。
- 如果A是表集合，B是基表，定义A=（table1, table2, ... ,table n）。此时的语义为：以集合A内各表做连接得到的联接表作为外表，B作为内表，两者做merge连接。（当B是表集合、A是基表时情况类似）
- 如果A、B均是表集合，则表示以集合A内各表做连接得到的联接表作为外表，以集合B内各表做连接得到的联接表作为内表，两者做merge连接。
举例：
对于基表a、b、c、d、e、f，用下列一套hint组合可准确指定这六张表的连接方式和顺序:
/*+use_nl((a,b,c,d),(e,f)) use_hj(a,(b,c,d)) use_mj(b,(c,d))
use_nl(c,d) use_nl(e,f)*/
上述hint语句表示的各表连接顺序和方式如下图 21所示：
图385 hint语义图
use_join (A, B)¶
use_join ::=
表示在做表连接时，A做外表，B做内表，但不指定具体连接方法。该hint的参数情况与use_nl类似。
如果只是想要指定多表连接的先后顺序，而不指定两表间的连接方法，使用该hint。
举例:
/*+ use_join (d,(a, b,c)) use_join ((a, b),c) use_join (a, b)*/
上述hint表示a做外表b做内表进行连接，然后的到的联结表（a,b）做外表，c做内表进行连接，最后得到的联结表（a,b,c）做内表d做外表进行连接；而所有连接的连接方法（nl、mj或者hj）均由优化器自己选择。
use_semi_nl (A, B)¶
use_semi_nl ::=
用在父查询中，表明A做外表，B做内表，两者做nestloop连接，连接方式为semi-join。
举例:
-- 清理环境
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
-- 创建表
CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);
explain select /*+ use_semi_nl(a,b@sel$2)*/ * from a where a.a1 in (
select b1 from b);
QUERY PLAN(text)
----------------------
Nested Loop(Left Semi Join) (cost=0.00..38643.87 rows=8192 width=8)
Join Filter: ("outer".A1 = "inner".B1)
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
-> Seq Scan on B (cost=0.00..89.92 rows=8192 width=4)
总数目：4
-- 删除表
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
use_semi_hj (A, B)¶
use_semi_hj ::=
用在父查询中，表明A做外表，B做内表，两者做hash join连接，连接方式为semi-join。
举例:
-- 清理环境
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
-- 创建表
CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);
CREATE TABLE c(c1 INT, c2 INT);
explain select /*+ use_semi_hj(a,(b@sel$2,c@sel$2))*/ * from a where a.a1 in (
select b1 from b,c where b.b1=c.c1 and c.c2<10);
QUERY PLAN(text)
----------------------
Hash Join(Left Semi Join) (cost=3978.13..4375.25 rows=8192 width=8)
Hash Cond: ("outer".A1 = "inner".B1)
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
-> Hash (cost=2580.03..2580.03 rows=111848 width=4)
-> Merge Join(Inner Join) (cost=888.65..2580.03 rows=111848 width=4)
Merge Cond: ("outer".C1 = "inner".B1)
-> Sort (cost=266.25..273.08 rows=2731 width=4)
Sort Key: C1
-> Seq Scan on C (cost=0.00..110.40 rows=2731 width=4)
Scan Key: (C2 < 10)
-> Sort (cost=622.40..642.88 rows=8192 width=4)
Sort Key: B1
-> Seq Scan on B (cost=0.00..89.92 rows=8192 width=4)
总数目：13
-- 删除表
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
use_semi_mj (A, B)¶
use_semi_mj ::=
用在父查询中，表明A做外表，B做内表，两者做merge join连接，连接方式为semi-join。
举例:
-- 清理环境
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
DROP TABLE d CASCADE;
-- 创建表
CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);
CREATE TABLE c(c1 INT, c2 INT);
CREATE TABLE d(d1 INT, d2 INT);
explain select /*+ use_semi_mj((a,b),(c@sel$2,d@sel$2))*/ * from a,b where a.a1=b.b1 and b.b1 in (
select c2 from c,d where c.c2=d.d2 and d.d1<10);
QUERY PLAN(text)
----------------------
Merge Join(Left Semi Join) (cost=2133.45..3318209.62 rows=335544 width=16)
Merge Cond: ("outer".B1 = "inner".C2)
-> Merge Join(Inner Join) (cost=1244.80..6318.92 rows=335544 width=16)
Merge Cond: ("outer".A1 = "inner".B1)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: A1
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: B1
-> Seq Scan on B (cost=0.00..89.92 rows=8192 width=8)
-> Merge Join(Inner Join) (cost=888.65..2580.03 rows=111848 width=4)
Merge Cond: ("outer".D2 = "inner".C2)
-> Sort (cost=266.25..273.08 rows=2731 width=4)
Sort Key: D2
-> Seq Scan on D (cost=0.00..110.40 rows=2731 width=4)
Scan Key: (D1 < 10)
-> Sort (cost=622.40..642.88 rows=8192 width=4)
Sort Key: C2
-> Seq Scan on C (cost=0.00..89.92 rows=8192 width=4)
总数目：19
-- 删除表
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
DROP TABLE d CASCADE;
use_nl_with_material (A, B)¶
表明A做外表，B做内表，两者做nestloop连接，并且采用material计划作为内表扫描计划，内外表做连接的jointype类型由优化器自己决定。
举例:
-- 清理环境
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
-- 创建表
CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);
CREATE TABLE c(c1 INT, c2 INT);
explain select /*+use_nl_with_material(a,(b,c))*/ * from a, b, c
where a.a1=b.b1 and b.b1=c.c1;
QUERY PLAN(text)
----------------------
Nested Loop(Inner Join) (cost=8293.46..75282600.44 rows=13743895 width=24)
Join Filter: ("outer".A1 = "inner".B1)
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
-> Materialize (cost=8293.46..13287.91 rows=335544 width=16)
-> Merge Join(Inner Join) (cost=1244.80..6318.92 rows=335544 width=16)
Merge Cond: ("outer".B1 = "inner".C1)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: B1
-> Seq Scan on B (cost=0.00..89.92 rows=8192 width=8)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: C1
-> Seq Scan on C (cost=0.00..89.92 rows=8192 width=8)
总数目：12
-- 删除表
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
use_semi_nl_with_material(A, B)¶
表明A做外表，B做内表，两者做nestloop连接，并且采用material计划作为内表扫描计划，内外表做连接的jointype类型为semi类型。
举例:
-- 清理环境
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
-- 创建表
CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);
CREATE TABLE c(c1 INT, c2 INT);
explain select /*+ use_semi_nl_with_material((a,b),c_alias@sel$2) */ * from a, b, c
where a.a1=b.b1 and b.b1 in (select c1 from c c_alias where c_alias.c2>6);
QUERY PLAN(text)
----------------------
Nested Loop(Inner Join) (cost=1456.04..18929237.59 rows=920840988 width=24)
-> Nested Loop(Left Semi Join) (cost=1357.93..512266.24 rows=112407 width=16)
Join Filter: ("outer".B1 = "inner".C1)
-> Merge Join(Inner Join) (cost=1244.80..6318.92 rows=335544 width=16)
Merge Cond: ("outer".A1 = "inner".B1)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: A1
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: B1
-> Seq Scan on B (cost=0.00..89.92 rows=8192 width=8)
-> Materialize (cost=113.13..140.44 rows=2731 width=4)
-> Seq Scan on C C_ALIAS (cost=0.00..110.40 rows=2731 width=4)
Scan Key: (C2 > 6)
-> Materialize (cost=98.11..180.03 rows=8192 width=8)
-> Seq Scan on C (cost=0.00..89.92 rows=8192 width=8)
总数目：16
-- 删除表
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
use_norm_nl_with_material(A, B)¶
表明A做外表，B做内表，两者做nestloop连接，并且采用material计划作为内表扫描计划，内外表做连接的jointype类型不是semi类型。
举例:
-- 清理环境
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
-- 创建表
CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);
CREATE TABLE c(c1 INT, c2 INT);
explain select /*+use_norm_nl_with_material(a,(b,c))*/ * from a, b, c
where a.a1=b.b1 and b.b1=c.c1;
QUERY PLAN(text)
----------------------
Nested Loop(Inner Join) (cost=8293.46..75282600.44 rows=13743895 width=24)
Join Filter: ("outer".A1 = "inner".B1)
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
-> Materialize (cost=8293.46..13287.91 rows=335544 width=16)
-> Merge Join(Inner Join) (cost=1244.80..6318.92 rows=335544 width=16)
Merge Cond: ("outer".B1 = "inner".C1)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: B1
-> Seq Scan on B (cost=0.00..89.92 rows=8192 width=8)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: C1
-> Seq Scan on C (cost=0.00..89.92 rows=8192 width=8)
总数目：12
-- 删除表
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
use_nl_with_index(A, b index_name)¶
注解
参数b只能是单表，不能是表集合，表名和索引名之间用空格分隔，该hint必须指定且只能指定一个索引名。 注意该hint与单表的index(a,[ index_1, … , index_n ])的不同。
表明A做外表，b做内表，两者做nestloop连接，并且内表条件下降，扫描采用指定索引，内外表做连接的jointype类型由优化器自己决定。
举例:
-- 清理环境
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
-- 创建表
CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);
CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX c2_index ON c(c2);
explain select /*+use_nl_with_index((a,b),c c2_index)*/ * from a, b, c
where a.a1=b.b1 and b.b2=c.c2;
QUERY PLAN(text)
----------------------
Nested Loop(Inner Join) (cost=1244.80..652418.47 rows=13743895 width=24)
-> Merge Join(Inner Join) (cost=1244.80..6318.92 rows=335544 width=16)
Merge Cond: ("outer".A1 = "inner".B1)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: A1
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: B1
-> Seq Scan on B (cost=0.00..89.92 rows=8192 width=8)
-> Index Scan using C2_INDEX(Normal Index Scan) on C (cost=0.00..1.41 rows=41 width=8)
Index Key: (C.C2 = "outer".B2)
总数目：11
-- 删除表
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
对于该hint的实现，将largs用来存储A，用rargs来存储b和索引。在process_single_table中传入rargs就可以保证原来hint的单表处理函数process_singletable_hints(Query *root, RelOptInfo *rel)的函数接口不变了。
use_norm_nl_with_index(A, b index_name)¶
表明A做外表，b做内表，两者做nestloop连接，并且内表条件下降，扫描采用指定索引，内外表做连接的jointype类型不是semi类型。
举例:
-- 清理环境
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
-- 创建表
CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);
CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX c2_index ON c(c2);
explain select /*+use_norm_nl_with_index((a,b),c c2_index)*/ * from a, b, c
where a.a1=b.b1 and b.b2=c.c2;
QUERY PLAN(text)
----------------------
Nested Loop(Inner Join) (cost=1244.80..652418.47 rows=13743895 width=24)
-> Merge Join(Inner Join) (cost=1244.80..6318.92 rows=335544 width=16)
Merge Cond: ("outer".A1 = "inner".B1)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: A1
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: B1
-> Seq Scan on B (cost=0.00..89.92 rows=8192 width=8)
-> Index Scan using C2_INDEX(Normal Index Scan) on C (cost=0.00..1.41 rows=41 width=8)
Index Key: (C.C2 = "outer".B2)
总数目：11
-- 删除表
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
use_semi_nl_with_index(A, b index_name)¶
表明A做外表，b做内表，两者做nestloop连接，并且内表条件下降，扫描采用指定索引，内外表做连接的jointype类型为semi类型。
举例:
-- 清理环境
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;
-- 创建表
CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);
CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX c1_index ON c(c1);
explain select /*+ use_semi_nl_with_index((a,b) , c_alias@sel$2 c1_index ) */ * from a, b, c
where a.a1=b.b1 and b.b1 in (select c1 from c c_alias where c_alias.c2>6);
QUERY PLAN(text)
----------------------
Nested Loop(Inner Join) (cost=1342.91..18437176.78 rows=920840988 width=24)
-> Nested Loop(Left Semi Join) (cost=1244.80..20205.43 rows=112407 width=16)
-> Merge Join(Inner Join) (cost=1244.80..6318.92 rows=335544 width=16)
Merge Cond: ("outer".A1 = "inner".B1)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: A1
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: B1
-> Seq Scan on B (cost=0.00..89.92 rows=8192 width=8)
-> Index Scan using C1_INDEX(Normal Index Scan) on C C_ALIAS (cost=0.00..1.52 rows=14 width=4)
Index Key: (C_ALIAS.C1 = "outer".B1)
Table Filter: (C2 > 6)
-> Materialize (cost=98.11..180.03 rows=8192 width=8)
-> Seq Scan on C (cost=0.00..89.92 rows=8192 width=8)
总数目：15
-- 删除表
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;