Help Center/ GaussDB/ Developer Guide(Centralized_2.x)/ Performance Tuning/ SQL Optimization/ Hint-based Tuning/ Hint for Materializing a Sub-plan Result
Updated on 2023-10-23 GMT+08:00
View PDF
Share

Hint for Materializing a Sub-plan Result

Function

You can materialize a sub-plan result to temporarily store the query record. This hint is used only in INSERT statements.

Syntax

material_subplan

Examples

Normal INSERT INTO...SELECT statement: 
insert into test select /*+ nestloop(test_src t1)*/ * from test_src where notexists(select 1 from test t1 where t1.a = test_src.a);
Execution plan: 
                       QUERY PLAN                        
---------------------------------------------------------
 Insert on test
   ->  Nested Loop Anti Join
         ->  Seq Scan on test_src
         ->  Index Only Scan using test_a_idx on test t1
               Index Cond: (a = test_src.a)
(5 rows)
Use the material_subplan hint operator: 
insert /*+ material_subplan*/ into test select /*+ nestloop(test_src t1)*/ * from test_src where not exists(select 1 from test t1 where t1.a = test_src.a);
The execution plan is as follows: 
                          QUERY PLAN                           
---------------------------------------------------------------
 Insert on test
   ->  Materialize
         ->  Nested Loop Anti Join
               ->  Seq Scan on test_src
               ->  Index Only Scan using test_a_idx on test t1
                     Index Cond: (a = test_src.a)
(6 rows)
Parent topic: Hint-based Tuning