Help Center/ GaussDB/ Developer Guide(Centralized_3.x)/ SQL Optimization/ Hint-based Tuning/ Sublink Name Hints
Updated on 2024-05-07 GMT+08:00
View PDF
Share

Sublink Name Hints

Description

These hints specify the name of a sublink block.

Syntax

1
 
blockname ( [@queryblock] table)

Parameters

  • The blockname hint is used by an outer query only when the corresponding sublink is not pulled up. Currently, only the Agg equivalent join, IN, and EXISTS sublinks can be pulled up. This hint is usually used together with the hints described in the previous sections.
  • The subquery after the FROM keyword is hinted by using the subquery alias. In this case, blockname becomes invalid.
  • If a sublink contains multiple tables, the tables will be joined with the outer-query tables in a random sequence after the sublink is pulled up. In this case, blockname also becomes invalid.

Examples

1
 
explain select /*+nestloop(store_sales tt) */ * from store_sales where ss_item_sk in (select /*+blockname(tt)*/ i_item_sk from item group by 1);

tt indicates the sublink block name. After being pulled up, the sublink is joined with the outer-query table store_sales by using nestloop. The optimized plan is as follows:

When the blockname hint is specified using @queryblock instead of taking effect in the current query block, for example, blockname(@sel$2 new_qb_name), other hints cannot be specified using @new_qb_name. In this case, new_qb_name is only used as the name of the sublink and can be specified using hints.
  • The following specifies block bn2 using blockname(@sel$2 bn2). As a result, TableScan(@bn2 t2) cannot find the queryblock by using @bn2. The query block should be specified by using @sel$2. The following specifies block bn3 using the blockname(bn3) hint. The hint takes effect in the current query block and changes the name of the query block. Therefore, tablescan(@bn3 t3@bn3) can find the query block specified by using @bn3. 
    gaussdb=# explain select /*+ blockname(@sel$2 bn2) tablescan(@bn2 t2) tablescan(@sel$2 t2@bn2) indexscan(@sel$2 t2@sel$2) tablescan(@bn3 t3@bn3)*/ c2 from t1 where c1 in ( select /*+ */t2.c1 from t2 where t2.c2 = 1 group by 1) and c3 in ( select /*+ blockname(bn3)*/t3.c3 from t3 where t3.c2 = 1 group by 1);
WARNING:  hint: TableScan(@bn2 t2) does not match any query block
WARNING:  Error hint: TableScan(@"sel$2" t2@bn2), relation name "t2@bn2" is not found.
  • The following specifies the sublink block bn2 by using blockname(@sel$2 bn2). When the sublink is promoted, hashjoin(t1 bn2) can be used to specify the operation of the promoted sublink. 
    gaussdb=# explain select /*+ blockname(@sel$2 bn2) hashjoin(t1 bn2) nestloop(t1 bn3) nestloop(t1 sel$3)*/ c2 from t1 where c1 in ( select /*+ */t2.c1 from t2 where t2.c2 = 1 group by 1)  and c3 in ( select /*+ blockname(bn3)*/t3.c3 from t3 where t3.c2 = 1 group by 1);
WARNING:  Duplicated or conflict hint: NestLoop(t1 "sel$3"), will be discarded.
Parent topic: Hint-based Tuning