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 Specifying the Query Block Where the Hint Is Located. @queryblock can be omitted, indicating that the hint 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 | ->  Streaming (type: GATHER)
  2 |    ->  Hash Join (3,4)
  3 |       ->  Seq Scan on se_t2
  4 |       ->  Hash
  5 |          ->  HashAggregate
  6 |             ->  Hash Join (7,8)
  7 |                ->  Seq Scan on se_t1
  8 |                ->  Hash
  9 |                   ->  Seq Scan on se_t3
(9 rows)

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

-- Scenarios where hints are used
gaussdb=# EXPLAIN(costs off) SELECT /*+ 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 | ->  Streaming (type: GATHER)
  2 |    ->  Hash Semi Join (3, 4)
  3 |       ->  Seq Scan on se_t2
  4 |       ->  Hash
  5 |          ->  HashAggregate
  6 |             ->  Hash Join (7,8)
  7 |                ->  Seq Scan on se_t1
  8 |                ->  Hash
  9 |                   ->  Seq Scan on se_t3
(9 rows)

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

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