QB_NAME

Introduction

In this article, we explore the Oracle SQL hint QB_NAME as part of our series on Oracle hints. The QB_NAME hint assigns a name to a query block, allowing developers and DBAs to explicitly reference that block in other hints. This is especially useful in complex SQL queries involving sub-queries, inline views, or set operations, where it's important to target specific parts of the SQL for optimization.

While QB_NAME by itself, does not affect the execution plan, it acts as a "label" that enables more precise use of other hints, such as USE_NL, INDEX, or NO_MERGE. By naming query blocks, you gain control over which hint applies to which part of the query.

As always, test, test, test.

 How Oracle Processes Query blocks

Internally, Oracle breaks SQL statements down into query blocks, which represent logically distinct parts of the statement. These blocks may correspond to the main query, sub-queries, or views (inline or otherwise).

Examples of query blocks include:

• The main SELECT statement

• A subquery in the WHERE clause

• A derived table in the FROM clause (inline view)
  
  

Oracle assigns internal names like SEL$1, SEL$2, etc., to each block.  For example, you may have seen these in query plan output as shown in the following

---------------------------------------------------------------------------------------------

| Id  | Operation                          | Name          | QB Name     | Object Alias     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |               | SEL$1       |                  |

|   1 |  NESTED LOOPS                      |               | SEL$1       |                  |

|   2 |   TABLE ACCESS BY INDEX ROWID      | DEPARTMENTS   | SEL$2       | D                |

|   3 |    INDEX RANGE SCAN                | DEPT_LOC_IDX  | SEL$2       | D                |

|   4 |   TABLE ACCESS BY INDEX ROWID      | EMPLOYEES     | SEL$1       | E                |

|   5 |    INDEX RANGE SCAN                | EMP_DEPT_IDX  | SEL$1       | E                |

---------------------------------------------------------------------------------------------

Outline Data:

  /*+

      BEGIN_OUTLINE_DATA

      INDEX_RS_ASC(@"SEL$2" "D"@"SEL$2" "DEPT_LOC_IDX")

      INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" "EMP_DEPT_IDX")

      USE_NL(@"SEL$1" "E"@"SEL$1" "D"@"SEL$2")

      END_OUTLINE_DATA

  */  

These system generated names are often difficult to follow. This is where the QB_NAME hint comes into its own, as it allows you to assign meaningful, user-defined names that can be referenced by other hints.  

Using the QB_NAME Hint Example

In this example, we label the outer query block as main_qb and the subquery block as dept_qb. 

SELECT /*+ QB_NAME(main_qb) */

    e.employee_id,

    e.first_name,

    e.last_name

FROM employees e

WHERE e.department_id IN (

    SELECT /*+ QB_NAME(dept_qb) */

        d.department_id

    FROM departments d

    WHERE d.location_id = 1700

)

/

Having added our QB_NAME, we can now apply other hints specifically to each block.  See below.

SELECT /*+ QB_NAME(main_qb e) 

            USE_NL(e) 

            INDEX(@dept_qb d DEPT_LOC_IDX) */

    e.employee_id,

    e.first_name,

    e.last_name

FROM employees e

WHERE e.department_id IN (

    SELECT /*+ QB_NAME(dept_qb) */

        d.department_id

    FROM departments d

    WHERE d.location_id = 1700

)

/

You will notice that we have labeled a block dept_qb and then used the name in the INDEX hint,  We have also labeled a block main_qb, but in this instance have not gone on to use it in a hint.  However, Oracle will still reference it in the query plan.  This is because, Oracle will make use of the user generated names internally, just as it would if the names had been system generated.  See below.

-------------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name          | Rows | Bytes | Cost | QB Name  | Alias   |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |               |      |       |      | MAIN_QB  |         |

|   1 |  NESTED LOOPS                      |               |    5 |   300 |    5 | MAIN_QB  |         |

|   2 |   TABLE ACCESS BY INDEX ROWID      | DEPARTMENTS   |    2 |    24 |    2 | DEPT_QB  | D       |

|   3 |    INDEX RANGE SCAN                | DEPT_LOC_IDX  |    2 |       |    1 | DEPT_QB  | D       |

|   4 |   TABLE ACCESS BY INDEX ROWID      | EMPLOYEES     |    5 |   100 |    3 | MAIN_QB  | E       |

|   5 |    INDEX RANGE SCAN                | EMP_DEPT_IDX  |    5 |       |    1 | MAIN_QB  | E       |

-------------------------------------------------------------------------------------------------------

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      QB_NAME(main_qb)

      QB_NAME(dept_qb)

      INDEX(@"DEPT_QB" "D"@"DEPT_QB" "DEPT_LOC_IDX")

      USE_NL(@"MAIN_QB" "E"@"MAIN_QB" "D"@"DEPT_QB")

      END_OUTLINE_DATA

  */

Considerations

Before using the QB_NAME hint, keep the following in mind

• QB_NAME is especially useful when troubleshooting execution plans, as it allows precise control over which hints affect which parts of the query.

• The effectiveness of QB_NAME depends on correct referencing in other hints (e.g., @block_name syntax). Misnaming or misplacement of hints will render them ineffective

• For deeply nested queries or those with multiple inline views, assigning names to query blocks can help both Oracle and the developer navigate optimization more clearly.

• If two or more query blocks have the same name, or if the same query block is hinted twice with different names, then the optimizer ignores all the names and the hints referencing that query block. 

• Query blocks that are not named have unique system generated names. 

• System generated names can also be used in hints within the query block, or in query block hints. 

Further Reading

• You can find more information on using the hint in the documentation 

Published on 

Published 7th May 2025