Hints for Inner Table Materialization During Join

Description

These hints materialize inner tables when specifying the inner tables to be joined.

Syntax

[no] materialize_inner([@queryblock] inner_table_list)

Parameters

• no indicates that the materialization of hints 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.
• inner_table_list: a list of inner tables to be materialized during the join operation. The value is a character string separated by spaces.

Example

Table t1 is an inner table to be materialized, and the result of (t1 t2) is materialized as a joined inner table.

gaussdb=# explain (costs off) select /*+materialize_inner(t1) materialize_inner(t1 t2)*/ * from t1,t2,t3 where  t1.c3 = t2.c3 and t2.c2=t3.c2 and t1.c2=5;
                       QUERY PLAN                       
--------------------------------------------------------
 Nested Loop
   Join Filter: (t2.c2 = t3.c2)
   ->  Seq Scan on t3
   ->  Materialize
         ->  Nested Loop
               Join Filter: (t1.c3 = t2.c3)
               ->  Seq Scan on t2
               ->  Materialize
                     ->  Bitmap Heap Scan on t1
                           Recheck Cond: (c2 = 5)
                           ->  Bitmap Index Scan on it3
                                 Index Cond: (c2 = 5)
(12 rows)