Use Query Block Name and QB_NAME Hint to control query optimization

When the optimizer of OceanBase Database analyzes a query, it decomposes the query into multiple query blocks (Query Block) based on the query structure. Each query block has a unique query block name (Query Block Name, abbreviated as QB Name). If you do not manually specify a QB name, the optimizer generates a QB name.

Syntax and rules of QB_NAME Hint

You can use the QB_NAME Hint to explicitly assign a QB name to 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 (view view_1) is named qb2:

SELECT /*+ QB_NAME(qb1) */ * FROM (SELECT /*+ QB_NAME(qb2) */ * FROM t1) view_1;

Considerations:

• QB names are case-insensitive.
• QB names cannot contain special characters.
• QB names are limited to a maximum of 20 characters.
• You cannot assign multiple QB names to the same query block or use the same QB name for different query blocks.

How to use Query Block Name

Define the scope of Query Block Hint

The Query Block Hint allows you to define its scope by specifying the Query Block Name within the hint.

For example, the hint index(@qb2 t1 idx) is declared in the query block qb1, but it is effective in the 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 Hint in the query

You can precisely control the target of the Hint in the query by specifying the Query Block Name.

By using the Query Block Name in the Hint, you can accurately specify the target.

For example, when using the leading Hint, if you do not explicitly specify the Query Block Name of the table, the Hint may be ineffective:

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  |
===========================

Rules for automatically generating Query Block Name

If you do not specify a QB name, the optimizer generates a QB 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 automatically generated names for different types of query blocks:

For example, the UNION operation causes the optimizer to automatically generate a Query Block Name for the query block containing these operations. 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 increasing sequence numbers to each query block from the outermost to the innermost. 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;

Without 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.

Changes in Query Block Name during query rewriting

When a query block triggers query rewriting, its Query Block Name can change. You can use the EXPLAIN EXTENDED command to track these changes in the qb name trace of the query plan.

To confirm the automatically generated Query Block Name before query rewriting, you can include the NO_QUERY_TRANSFORMATION Hint in the query to disable all query rewriting, and then use EXPLAIN OUTLINE to obtain the OUTLINE DATA. By analyzing the FULL and INDEX Query Block Hints in the OUTLINE DATA, you can verify the name of each Query Block.

For example, in this query, the OUTLINE DATA FULL(@"SEL$2" "test"."t1"@"SEL$2") 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;