Hints for Specifying the Query Block and Schema of a Table

Description

In a query, the table name can be duplicate in different query blocks and different schemas. Therefore, when specifying a table in a query, you can use a hint to specify the query block and schema where the table is located to avoid ambiguity. This function is applicable to all hints whose table names need to be specified.

Syntax

When specifying a table using a hint, specify a schema using a period (schema.) and a query block using the at sign (@queryblock). Both the schema and query block can be left blank.

[schema.]relname[@queryblock]

Parameters

• relname indicates the name of the table in the query. If the table has an alias, use the alias first. In this case, relname is set to the alias. If the table name contains special characters, such as at sign (@) and period (.), relname must be enclosed in double quotation marks ("") to avoid conflict with the declaration of the query block and schema names. For example, if the table name is relnametest@1, enter "relnametest@1".
• schema indicates the schema where the table is located. It can be left empty. If no schema is specified, the hint searches all schemas for relname.
• queryblock indicates the query block where the table is located. It can be left empty. If no query block is specified, the hint searches all query blocks for relname.

Example

1. t1 of sel$2 is promoted to sel$1 for processing, and t1 is unclear.

   gaussdb=# explain(blockname on,costs off) select /*+ indexscan(t1)*/ * from t1, (select c2 from t1 where c1=1) tt1 where t1.c1 = tt1.c2; 
   WARNING:  Error hint: IndexScan(t1), relation name "t1" is ambiguous.
   ...

2. t1@sel$2 is specified to perform indexscan on t1 of sel$2 (Index Cond: (c1 = 1)).

   gaussdb=# explain(blockname on,costs off) select /*+ indexscan(t1@sel$2)*/ * from t1, (select c2 from t1 where c1=1) tt1 where t1.c1 = tt1.c2; 
    id |                     operation                     | Query Block 
   ----+---------------------------------------------------+-------------
     1 | ->  Hash Join (2,3)                               | sel$1
     2 |    ->  Seq Scan on t1@"sel$1"                     | sel$1
     3 |    ->  Hash                                       | 
     4 |       ->  Index Only Scan using it1 on t1@"sel$2" | sel$1
   (4 rows)
   
     Predicate Information (identified by plan id)   
   --------------------------------------------------
      1 --Hash Join (2,3)
            Hash Cond: (public.t1.c1 = public.t1.c2)
      4 --Index Only Scan using it1 on t1@"sel$2"
            Index Cond: (c1 = 1)
   (4 rows)