Hint Specifying the Query Block Where the Hint Is Located

Description

This function allows users to use @queryblock in hints to implement block-level hint control. Users can specify the query block to which the hint takes effect. For example, you can specify the hint of an inner query block in the outer query block.

Syntax

Add @queryblock at the beginning of the hint parameter. Hint_SEPC is a specific hint.

Hint_SEPC([@queryblock])

Parameters

Hint_SEPC is the hint name, and @queryblock can be left empty. If @queryblock is left empty, the hint takes effect in the current query block declared by the hint. If @queryblock is left empty and Hint_SPEC has no parameter, use Hint_SPEC instead of Hint_SPEC(). Parentheses are unnecessary. The following describes how to name a query block and how to make a hint take effect. Some hints do not take effect only at the outermost layer and cannot be specified using @queryblock. For details, see the syntax description of each hint.

• Query the name of a query block.

  Each query block must have a name, so as to accurately specify a hint. There are two naming methods: user-specified and system-specified.

  1. You can use the blockname hint to specify the block to be queried. For details, see Sublink Name Hints.
  2. If no alias is specified for a query block, the default block name is automatically generated based on the processing sequence. Generally, the default alias of each query block consists of the first three letters of the query block name, $, and the number of the query block. For example, the alias of the first SELECT query block is sel$1. In pretty mode, you can use the explain method with a specified block name to view the name of the query block where the processing operator of each table is located.

  gaussdb=# set explain_perf_mode = pretty;
  SET
  gaussdb=# explain (blockname on,costs off) select * from t1, (select c1 from t2 group by c1) sub1 where t1.c1 = sub1.c1;
   id |              operation              | Query Block 
  ----+-------------------------------------+-------------
    1 | ->  Hash Join (2,3)                 | sel$1
    2 |    ->  Seq Scan on t1@"sel$1"       | sel$1
    3 |    ->  Hash                         | 
    4 |       ->  HashAggregate             | sel$2
    5 |          ->  Seq Scan on t2@"sel$2" | sel$2
  (5 rows)

  You can see that Seq Scan of t2 is located in the sel$2 query block.

• @queryblock specifies the query block.

  For the preceding example, if you want to modify the indexscan mode in t2, run the following command:

  select /*+indexscan(@sel$2 t2) tablescan(t1)*/ * from t1, (select c1 from t2 group by c1) sub1 where t1.c1 = sub1.c1; 

  Both indexscan and tablescan are scan hints. For details about scan hints, see Scan Operation Hints. You can specify the hint of indexscan(@sel$2 t2) in the sel$1 query block to move the hint to the sel$2 query block. The hint takes effect for t2. If the sel$2 query block is promoted sel$1 during subsequent rewriting, the hint is also promoted together to sel$1 and continues to take effect for t2.

  gaussdb=#  explain (blockname on,costs off) select /*+indexscan(@sel$2 t2) tablescan(t1)*/ * from t1, (select c1 from t2 group by c1) sub1 where t1.c1 = sub1.c1;
   id |                      operation                       | Query Block 
  ----+------------------------------------------------------+-------------
    1 | ->  Hash Join (2,3)                                  | sel$1
    2 |    ->  Seq Scan on t1@"sel$1"                        | sel$1
    3 |    ->  Hash                                          | 
    4 |       ->  Group                                      | sel$2
    5 |          ->  Index Only Scan using it2 on t2@"sel$2" | sel$2
  (5 rows)

  

  Sometimes, query rewriting in the optimizer phase expands some query blocks. As a result, the plan does not display related query blocks in the explain method. The hint specifies a query block based on the name of the query block before the optimizer phase. If a query block to be queried may be expanded in the planning phase, you can add the no_expand hint (see Hints Specifying Not to Expand Subqueries) to prevent it from being expanded.

  1. The sel$2 query block is a simple query. The optimizer performs query rewriting during subsequent processing, and t1 is promoted to sel$1 for processing. Therefore, the operation in the sel$2 query block is not displayed in the plan.

     gaussdb=# explain (blockname on,costs off) select * from t2, (select c1 from t1 where t1.c3 = 2) sub1 where t2.c1 = sub1.c1;
      id |                operation                 | Query Block 
     ----+------------------------------------------+-------------
       1 | ->  Hash Join (2,3)                      | sel$1
       2 |    ->  Seq Scan on t2@"sel$1"            | sel$1
       3 |    ->  Hash                              | 
       4 |       ->  Bitmap Heap Scan on t1@"sel$2" | sel$1
       5 |          ->  Bitmap Index Scan using it3 | 
     (5 rows)

  2. The sel$2 query block is a simple query. During subsequent processing, the optimizer skips query rewriting because of the no_expand hint, and t1 is still processed in the original query block.

     gaussdb=# explain (blockname on,costs off) select * from t2, (select /*+ no_expand*/ c1 from t1 where t1.c3 = 2) sub1 where t2.c1 = sub1.c1;
      id |                operation                 | Query Block 
     ----+------------------------------------------+-------------
       1 | ->  Hash Join (2,3)                      | sel$1
       2 |    ->  Seq Scan on t2@"sel$1"            | sel$1
       3 |    ->  Hash                              | 
       4 |       ->  Bitmap Heap Scan on t1@"sel$2" | sel$2
       5 |          ->  Bitmap Index Scan using it3 | 
     (5 rows)

  3. Because t1 is processed in the sel$2 query block after the no_expand hint is added, you can use @sel$2 to specify the query block for the hint.

     gaussdb=# explain (blockname on,costs off) select/*+ indexscan(@sel$2 t1)*/ * from t2, (select c1 from t1 where t1.c3 = 2) sub1 where t2.c1 = sub1.c1;
      id |                  operation                   | Query Block 
     ----+----------------------------------------------+-------------
       1 | ->  Hash Join (2,3)                          | sel$1
       2 |    ->  Seq Scan on t2@"sel$1"                | sel$1
       3 |    ->  Hash                                  | 
       4 |       ->  Index Scan using it3 on t1@"sel$2" | sel$1
     (4 rows)

  4. The query block number in the view depends on the sequence of the statement using the view. Therefore, do not use hints to specify query blocks when creating a view. The behavior is uncontrollable.

     gaussdb=# create view v1 as select/*+ no_expand */ c1 from t1 where c1 in (select /*+ no_expand */ c1 from t2 where t2.c3=4 );
     CREATE VIEW
     gaussdb=# explain (blockname on,costs off) select  * from v1;
      id |                   operation                   | Query Block 
     ----+-----------------------------------------------+-------------
       1 | ->  Seq Scan on t1@"sel$2"                    | sel$2
       2 |    ->  Seq Scan on t2@"sel$3"  [1, SubPlan 1] | sel$3
     (2 rows)
     
      Predicate Information (identified by plan id) 
     -----------------------------------------------
        1 --Seq Scan on t1@"sel$2"
              Filter: (hashed SubPlan 1)
        2 --Seq Scan on t2@"sel$3"
              Filter: (c3 = 4)
     (4 rows)

     In this case, the statements in v1 belong to sel$2 and sel$3.

  5. Some hints do not take effect only at the outermost layer and cannot be specified using @queryblock. For details, see the syntax description of each hint.