When the optimizer of OceanBase AI Database analyzes a query, it decomposes the query into multiple query blocks. Each block has a unique Query Block Name (QB Name). If not specified manually, the optimizer automatically generates a QB Name.
Syntax and rules of the QB_NAME hint
You can use the QB_NAME hint to explicitly allocate a QB name for a query block. The syntax is as follows:
/*+ QB_NAME(qb_name) */
In the following example, the outer query block is named qb1, and the inline view (within view view_1) is named qb2:
SELECT /*+ QB_NAME(qb1) */ * FROM (SELECT /*+ QB_NAME(qb2) */ * FROM t1) view_1;
Instructions and considerations:
- QB Name is case-insensitive.
- The QB name cannot contain special characters.
- The length of QB Name is limited to at most 20 characters.
- You cannot assign multiple QB Names to the same query block, nor can different blocks use the same QB Name.
How to use Query Block Name
Define the scope of the query block hint
Query block hints allow you to define the scope of a hint by specifying a query block name within the hint.
As shown in the following example, the hint index(@qb2 t1 idx) is declared in query block qb1 but specifies that it takes effect in query block qb2:
SELECT /*+ INDEX(@qb2 t1 idx) QB_NAME(qb1) */ * FROM (SELECT /*+ NO_MERGE QB_NAME(qb2) */ * FROM t1) view_1;
Specify the target of a hint in a query
Specify the target of a hint in a query to precisely control the object affected by the hint.
By using the query block name in a hint, you can precisely specify the target.
As shown in the following example, when using the leading hint, if the query block name of the table is not explicitly specified, the hint may become invalid:
SELECT /*+ LEADING(t1@sel$1 t1@sel$2) */ * FROM t1, (SELECT * FROM t1) v WHERE t1.c2 = v.c2;
Query Plan
===========================
|ID|OPERATOR |NAME|
---------------------------
|0 |HASH JOIN | |
|1 |├─TABLE FULL SCAN|t1 |
|2 |└─TABLE FULL SCAN|t1 |
===========================
Automatic generation rules for query block names
If the QB Name is not specified, the optimizer automatically generates a Query Block Name in the following format:
query_block_type_str$idx
Here, query_block_type_str$ is a string that identifies the type of the query block, and idx is the sequence number of the query block.
The following table shows examples of names automatically generated for different types of query blocks:
Query Block |
Example |
|---|---|
| SELECT | SEL$1 |
| UNION ALL/UNION/INTERSECT/MINUS | SET$1 |
| INSERT | INS$1 |
| UPDATE | UPD$1 |
| DELETE | DEL$1 |
| MERGE | MRG$1 |
| REPLACE | REP$1 |
| INSERT ALL | INS_ALL$1 |
As shown in the following example, UNION causes the optimizer to automatically generate a Query Block Name for the query block containing these operations. For example, in a query using UNION, the query block is automatically named SET$1:
SELECT /*+ USE_HASH_SET(@SET$1) */ c1, c2 FROM t1
UNION
SELECT c1, c2 FROM t2;
In this example, the hint /*+USE_HASH_SET(@SET$1) */ instructs the optimizer to use the HASH DISTINCT SET algorithm for the UNION operation.
Query block name in complex queries
When parsing more complex queries, the optimizer assigns an incremental number to each query block in a from-outside-to-inside order. If you remove the manually specified QB_NAME hint, the automatically generated query block names will be as follows:
SELECT /*+ QB_NAME(qb1) */ * FROM (SELECT /*+ QB_NAME(qb2) */ * FROM t1) v;
After removing the QB_NAME hint, the automatically generated name for the outer query block qb1 will be SEL$1, and the name for the inner block qb2 will be SEL$2.
Query rewrite and changes in query block names
When a query block triggers query rewrite, its Query Block Name may change. You can use the EXPLAIN EXTENDED command to track these changes in the qb name trace section of the query plan.
To confirm the automatically generated Query Block Name before query rewrite, you can include the NO_QUERY_TRANSFORMATION hint in the query to disable all query rewrite, and then use EXPLAIN OUTLINE to obtain the OUTLINE DATA. By analyzing Query Block hints such as FULL and INDEX in the OUTLINE DATA, you can verify the name of each Query Block.
For example, in this query, FULL(@"SEL$2" "test"."t1"@"SEL$2") in OUTLINE DATA indicates that the Query Block Name in the inline view v is SEL$2:
SELECT /*+ NO_QUERY_TRANSFORMATION */ COUNT(*)
FROM (SELECT * FROM t1) v GROUP BY c1;