Help Center> GaussDB> Distributed_8.x> SQL Optimization> Hint-based Tuning> Hint for Inner Table Materialization During Join
Updated on 2024-06-03 GMT+08:00
View PDF

Hint for Inner Table Materialization During Join

Function

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 Specifying the Query Block Where the Hint Is Located. @queryblock can be omitted, indicating that the hint 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                              
----------------------------------------------------------------------
 Streaming (type: GATHER)
   Node/s: All datanodes
   ->  Nested Loop
         Join Filter: (t2.c2 = t3.c2)
         ->  Seq Scan on t3
         ->  Materialize
               ->  Streaming(type: BROADCAST)
                     Spawn on: All datanodes
                     ->  Nested Loop
                           Join Filter: (t1.c3 = t2.c3)
                           ->  Seq Scan on t2
                           ->  Materialize
                                 ->  Streaming(type: BROADCAST)
                                       Spawn on: All datanodes
                                       ->  Index Scan using it3 on t1
                                             Index Cond: (c2 = 5)
(16 rows)
Parent topic: Hint-based Tuning