Use query block name and QB_NAME hint to control query optimization

When the OceanBase optimizer analyzes a query, it decomposes the query into multiple query blocks based on the query structure. Each block has a unique query block name (QB name). If not specified manually, the optimizer will automatically generate the QB name.

Syntax and rules of the 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 (in the view_1 view) is named qb2:

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

Considerations:

• QB names are case-insensitive.
• A QB name cannot contain special characters.
• A QB name must be 20 characters or less in length.
• You cannot assign multiple QB names to the same query block or use the same QB name for different blocks.

How to use query block name

Define the scope of query block hints

Query block hints allow you to define the scope of a Hint by specifying a query block name within the Hint.

For example, the index(@qb2 t1 idx) hint is declared in the query block qb1, but takes effect 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 objects to which hints apply in a query

You can precisely control the objects to which a hint in a query applies.

You can specify the query block name in the hint to precisely specify the objects to which the hint applies.

For example, when you use the leading hint, the hint may be invalid if you do not specify the table name with the query block name.

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 of query block name

If you do not specify a QB name, the optimizer automatically generates a QB name in the following format:

query_block_type_str$idx

In this format, query_block_type_str$ is a string that identifies the query block type, and idx is the serial number that identifies the query block.

The following table shows examples of automatically generated names for different types of query blocks.

For example, the optimizer automatically generates a query block name for a query block that contains the UNION operator. For example, in a query that contains the UNION operator, 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 serial number to each query block in an inner-to-outer 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 you remove the QB_NAME hint, the automatically generated name of the outer query block qb1 will be SEL$1, and the name of the inner query block qb2 will be SEL$2.

Query rewrite and changes of query block name

When a query block is involved in query rewrite, its query block name can be changed. You can use the EXPLAIN EXTENDED statement 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 add the NO_QUERY_TRANSFORMATION hint to the query to disable all query rewrites, and then use the EXPLAIN OUTLINE statement to obtain the OUTLINE DATA. By analyzing the Query Block Hints such as FULL, INDEX, and LEADING in the OUTLINE DATA, you can verify the name of each query block.

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