Hints for Specifying Whether to Use Semi-Join

Description

Specifies whether to select semi-join.

Syntax

[no] semijoin([@queryblock] table_list)

Parameters

• no indicates that semi-join is not used.

• For details about @queryblock, see Hint for Specifying the Query Block Where the Hint Is Located. This parameter can be omitted, indicating that it takes effect in the current query block.
• table_list specifies the tables to be joined. The values are the same as those of join_table_list but contain no parentheses.
• semijoin specifies whether semi-join is used for table join.

Examples

-- Preparation
CREATE TABLE se_t1 (a int, b int);
CREATE TABLE se_t2 (a int, b int);
CREATE TABLE se_t3 (a int, b int);
set explain_perf_mode = pretty;  -- Open the explain pretty option to view a detailed plan.
-- Scenarios where hints are not used
gaussdb=# EXPLAIN(costs off) SELECT a FROM se_t2 WHERE a IN( SELECT  se_t1.a FROM se_t1,se_t3 WHERE se_t1.a=se_t3.a GROUP BY 1);
 id |              operation               
----+--------------------------------------
  1 | ->  Hash Semi Join (2, 3)
  2 |    ->  Seq Scan on se_t2
  3 |    ->  Hash
  4 |       ->  HashAggregate
  5 |          ->  Hash Join (6,7)
  6 |             ->  Seq Scan on se_t1
  7 |             ->  Hash
  8 |                ->  Seq Scan on se_t3
(8 rows)

 Predicate Information (identified by plan id) 
-----------------------------------------------
   1 --Hash Semi Join (2, 3)
         Hash Cond: (se_t2.a = se_t1.a)
   5 --Hash Join (6,7)
         Hash Cond: (se_t1.a = se_t3.a)
(4 rows)

-- Use the semijoin hint to disable semi-join.
gaussdb=# EXPLAIN(costs off) SELECT /*+ NO SemiJoin(se_t2@"sel$1" "sel$2") */ a FROM se_t2 WHERE a IN( SELECT  se_t1.a FROM se_t1,se_t3 WHERE se_t1.a=se_t3.a GROUP BY 1);
 id |              operation               
----+--------------------------------------
  1 | ->  Hash Join (2,3)
  2 |    ->  Seq Scan on se_t2
  3 |    ->  Hash
  4 |       ->  HashAggregate
  5 |          ->  Hash Join (6,7)
  6 |             ->  Seq Scan on se_t1
  7 |             ->  Hash
  8 |                ->  Seq Scan on se_t3
(8 rows)

 Predicate Information (identified by plan id) 
-----------------------------------------------
   1 --Hash Join (2,3)
         Hash Cond: (se_t2.a = se_t1.a)
   5 --Hash Join (6,7)
         Hash Cond: (se_t1.a = se_t3.a)
(4 rows)

You can see that the semijoin hint can determine whether to use semi-join.