hash join最好以树形计划为准,对于pg和mysql,树形会将probe table写在hash table前面，hash table有Hash标志，而oracle会将hash table写在前面

1）普通hash join的join_prefix,join_order...驱动表按照hash表计算，树形计划显示在probe table下面，表格计划显示在上面

2）外连接，子查询semi/anti的则驱动表是probe table,如果是nested loops,显示在上面，如果是hash join,则驱动表是probe table,

   hash table显示在下面，

   树形计划显示在前面，表格计划也是显示在上面

3）子查询如果不是用firstmatch的hash join,则都是转为普通join,按照1）的顺序

1）普通hash join,表格执行计划顺序按照hash table-->probe table来，树形计划是probe table--->hash table来，驱动表是hash table,

注意顺序解读是先hash table--->然后probe table--->最后探测hash table,如果hash table没有结果，则probe table不执行

-- 普通的hash join,表格执行计划里虽然s2有using join buffer,但是实际上s1才是hash table

explain  select/*+no_index(s1) no_index(s2)*/ * from s1  join s2 on s1.id=s2.id;

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                      |

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+

|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  9576 |   100.00 | NULL                                       |

|  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99691 |     0.00 | Using where; Using join buffer (hash join) |

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+

2 rows in set, 1 warning (0.00 sec)

-- 树形计划看的很清楚，s1是hash table(有个Hash),s2是probe table,如果按照join_prefix指定驱动表，普通join hash table才是驱动表

explain analyze

select/*+no_index(s1) no_index(s2)*/ * from s1  join s2 on s1.id=s2.id\G

*************************** 1. row ***************************

EXPLAIN: -> Inner hash join (s2.id = s1.id)  (cost=95466409.24 rows=9576) (actual time=11.585..129.004 rows=10000 loops=1)

    -> Table scan on s2  (cost=0.14 rows=99691) (actual time=0.017..68.745 rows=100000 loops=1)

    -> Hash

        -> Table scan on s1  (cost=981.85 rows=9576) (actual time=0.032..8.449 rows=10000 loops=1)

1 row in set (0.14 sec)

-- 可以看到使用join_prefix(s2),hash table是s2,这个是驱动表

explain select/*+ join_prefix(s2) no_index(s1) no_index(s2)*/ * from s1  join s2 on s1.id=s2.id;

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                      |

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+

|  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99691 |   100.00 | NULL                                       |

|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  9576 |     0.01 | Using where; Using join buffer (hash join) |

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+

2 rows in set, 1 warning (0.00 sec)

explain analyze

select/*+ join_prefix(s2) no_index(s1) no_index(s2)*/ * from s1  join s2 on s1.id=s2.id\G

*************************** 1. row ***************************

EXPLAIN: -> Inner hash join (s1.id = s2.id)  (cost=95476435.27 rows=99691) (actual time=95.904..140.818 rows=10000 loops=1)

    -> Table scan on s1  (cost=0.02 rows=9576) (actual time=0.036..7.398 rows=10000 loops=1)

    -> Hash

        -> Table scan on s2  (cost=10105.35 rows=99691) (actual time=0.034..69.168 rows=100000 loops=1)

2)对于left join,probe table是主表，hash表只能是从表，和普通join不一样，也即probe table肯定是主表。join_order等没有用

-- 指定s1为hash表，没有效果，hash表是从表s2

explain  select/*+ join_prefix(s1) no_index(s1) no_index(s2)*/ * from s1 left join s2 on s1.id=s2.id;

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                      |

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+

|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  9576 |   100.00 | NULL                                       |

|  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99691 |   100.00 | Using where; Using join buffer (hash join) |

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+--------------------------------------------+

2 rows in set, 1 warning (0.00 sec)

-- 同样可以看出

explain analyze select/*+ join_prefix(s1) no_index(s1) no_index(s2)*/ * from s1 left join s2 on s1.id=s2.id\G

*************************** 1. row ***************************

EXPLAIN: -> Left hash join (s2.id = s1.id)  (cost=95465428.57 rows=954641016) (actual time=88.973..134.915 rows=10000 loops=1)

    -> Table scan on s1  (cost=981.85 rows=9576) (actual time=0.019..7.510 rows=10000 loops=1)

    -> Hash

        -> Table scan on s2  (cost=1.18 rows=99691) (actual time=0.030..64.026 rows=100000 loops=1)

1 row in set (0.14 sec)

3）对于子查询，如果走semi join/anti join,则驱动表只能是主表,如果走hash semi/anti则主表是probe table

explain select/*+join_prefix(s1) no_index(s2@subq) no_semijoin(@subq materialization)*/ * from s1 where exists(select/*+qb_name(subq) no_index(s2)*/ 1 from s2 where s1.id=s2.id);

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+------------------------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                                      |

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+------------------------------------------------------------+

|  1 | SIMPLE      | s1    | NULL       | ALL  | idx_s1        | NULL | NULL    | NULL |  9576 |   100.00 | NULL                                                       |

|  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99691 |     0.00 | Using where; FirstMatch(s1); Using join buffer (hash join) |

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+------------------------------------------------------------+

2 rows in set, 3 warnings (0.00 sec)

-- 走firtmatch hash semijoin

explain analyze

select/*+join_prefix(s1) no_index(s2@subq) no_semijoin(@subq materialization)*/ * from s1 

where exists(select/*+qb_name(subq) no_index(s2)*/ 1 from s2 where s1.id=s2.id)\G

*************************** 1. row ***************************

EXPLAIN: -> Hash semijoin (s2.id = s1.id)  (cost=95465427.53 rows=9576) (actual time=73.508..100.275 rows=10000 loops=1)

    -> Table scan on s1  (cost=981.85 rows=9576) (actual time=0.042..7.234 rows=10000 loops=1)

    -> Hash

        -> Table scan on s2  (cost=0.14 rows=99691) (actual time=0.040..56.276 rows=100000 loops=1)

1 row in set, 2 warnings (0.11 sec)

-- 按照hash表是驱动表计算

explain analyze

select/*+join_prefix(s2@subq) semijoin(@subq materialization) no_index(s1)*/ * from s1 

where exists(select/*+qb_name(subq)*/ 1 from s2 where s1.id=s2.id)\G

    -> where exists(select/*+qb_name(subq)*/ 1 from s2 where s1.id=s2.id)\G

*************************** 1. row ***************************

EXPLAIN: -> Inner hash join (s1.id = `<subquery2>`.id)  (cost=95485531.94 rows=99691) (actual time=159.732..192.653 rows=10000 loops=1)

    -> Table scan on s1  (cost=107.35 rows=9576) (actual time=0.030..7.666 rows=10000 loops=1)

    -> Hash

        -> Table scan on <subquery2>  (cost=20074.46..21323.09 rows=99691) (actual time=138.109..145.590 rows=100000 loops=1)

            -> Materialize with deduplication  (cost=20074.45..20074.45 rows=99691) (actual time=138.106..138.106 rows=100000 loops=1)

                -> Filter: (s2.id is not null)  (cost=10105.35 rows=99691) (actual time=0.034..86.307 rows=100000 loops=1)

                    -> Covering index scan on s2 using idx_s2  (cost=10105.35 rows=99691) (actual time=0.033..77.710 rows=100000 loops=1)

1 row in set, 1 warning (0.20 sec)

-- 子查询如果驱动表不是主表，则会改成普通join形式，除了 firstmatch,其他的如materialize,duplicate sweedout等都是改为普通join

explain analyze

select/*+join_prefix(s2@subq)*/ * from s1 

where exists(select/*+qb_name(subq) no_index(s2)*/ 1 from s2 where s1.id=s2.id)\G

*************************** 1. row ***************************

EXPLAIN: -> Remove duplicate s1 rows using temporary table (weedout)  (cost=44997.20 rows=99691) (actual time=0.062..373.483 rows=10000 loops=1)

    -> Nested loop inner join  (cost=44997.20 rows=99691) (actual time=0.056..366.303 rows=10000 loops=1)

        -> Filter: (s2.id is not null)  (cost=10105.35 rows=99691) (actual time=0.030..95.883 rows=100000 loops=1)

            -> Table scan on s2  (cost=10105.35 rows=99691) (actual time=0.029..86.925 rows=100000 loops=1)

        -> Index lookup on s1 using idx_s1 (id=s2.id)  (cost=2.60 rows=1) (actual time=0.002..0.003 rows=0 loops=100000)

1 row in set, 1 warning (0.38 sec)

4)对于anti join:not in,not exists的，nested loops只能是主表做驱动表，如果是hash join,也即probe table是主表

explain analyze

select * from s1 

where not exists(select/*+qb_name(subq)*/ 1 from s2 where s1.id=s2.id)\G

*************************** 1. row ***************************

EXPLAIN: -> Nested loop antijoin  (cost=4333.45 rows=9576) (actual time=32.817..32.817 rows=0 loops=1)

    -> Table scan on s1  (cost=981.85 rows=9576) (actual time=0.033..11.058 rows=10000 loops=1)

    -> Covering index lookup on s2 using idx_s2 (id=s1.id)  (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=10000)

1 row in set, 1 warning (0.03 sec)

-- 指定子查询为hash table

explain analyze

select/*+join_prefix(s2@subq) no_index(s1) semijoin(@subq materialization)*/ * from s1 

where not exists(select/*+qb_name(subq)*/ 1 from s2 where s1.id=s2.id)\G

*************************** 1. row ***************************

EXPLAIN: -> Nested loop antijoin  (cost=95466041.05 rows=954641016) (actual time=142.133..142.133 rows=0 loops=1)

    -> Table scan on s1  (cost=981.85 rows=9576) (actual time=0.034..9.019 rows=10000 loops=1)

    -> Single-row index lookup on <subquery2> using <auto_distinct_key> (id=s1.id)  (actual time=0.013..0.013 rows=1 loops=10000)

        -> Materialize with deduplication  (cost=20074.45..20074.45 rows=99691) (actual time=125.353..125.353 rows=100000 loops=1)

            -> Filter: (s2.id is not null)  (cost=10105.35 rows=99691) (actual time=0.009..78.961 rows=100000 loops=1)

                -> Covering index scan on s2 using idx_s2  (cost=10105.35 rows=99691) (actual time=0.008..70.918 rows=100000 loops=1)

1 row in set, 1 warning (0.14 sec)

-- nested loops只能主表驱动

explain analyze

select/*+join_prefix(s2@subq)*/ * from s1 

where not exists(select/*+qb_name(subq)*/ 1 from s2 where s1.id=s2.id)\G

*************************** 1. row ***************************

EXPLAIN: -> Nested loop antijoin  (cost=4333.45 rows=9576) (actual time=32.576..32.576 rows=0 loops=1)

    -> Table scan on s1  (cost=981.85 rows=9576) (actual time=0.031..11.154 rows=10000 loops=1)

    -> Covering index lookup on s2 using idx_s2 (id=s1.id)  (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=10000)

1 row in set, 1 warning (0.03 sec)