Understanding the QB_NAME Hint Issue in Oracle MERGE

When tuning complex SQL queries in Oracle, the QB_NAME (Query Block Name) hint is incredibly useful. It allows you to assign a custom name to a specific query block, making it easy to apply outer-level hints (like USE_HASH or LEADING) to stabilize execution plans.

However, when you attempt to use QB_NAME inside a MERGE statement—especially when using inline views in the INTO or USING clauses—you will often see the hints marked as Unused (U) in the execution plan's Hint Report, accompanied by the warning:

same QB_NAME hints for different query blocks

In this article, we'll explore why this happens and how to successfully apply join hints to your Oracle MERGE statements without relying on unstable system-generated query block names (like SEL$76AA3327).

Why Does Oracle Reject QB_NAME in MERGE Statements?

Oracle's Cost-Based Optimizer (CBO) performs various query transformations before generating an execution plan. When you write a MERGE statement with inline views, such as:

MERGE /*+ QB_NAME(mrg) */
INTO (SELECT * FROM t1) x
USING (SELECT /*+ QB_NAME(src) */ * FROM t2) z...

The optimizer internally transforms and merges these inline views into the main query block. During this transformation process, the query blocks containing your custom QB_NAME hints are copied, split, or merged. This causes the same custom name (e.g., src) to be associated with multiple transformed query blocks, violating Oracle's rule that query block names must be unique. Consequently, Oracle invalidates and ignores the hints.

How to Fix It: 3 Proven Solutions

Solution 1: Simplify the MERGE Syntax (Recommended)

The cleanest way to avoid query block transformation issues is to remove unnecessary inline views. Instead of wrapping your target and source tables in SELECT * FROM table, reference the tables directly in the INTO and USING clauses. This simplifies the execution plan and allows you to apply hints directly at the top level.

MERGE /*+ LEADING(z x) USE_HASH(x) */
INTO t1 x
USING t2 z
ON (x.col_id1 = z.col_id1 AND x.col_id2 = z.col_id2)
WHEN MATCHED THEN UPDATE
SET x.col3 = z.col3;

By referencing table aliases x and z directly in the top-level hint, Oracle knows exactly how to join them without needing custom query block names.

Solution 2: Use the NO_MERGE Hint to Preserve Block Names

If your USING clause requires a complex subquery or inline view that cannot be simplified, you must prevent Oracle from merging that query block. You can achieve this by combining the QB_NAME hint with the NO_MERGE hint inside the inline view.

MERGE INTO t1 x
USING (SELECT /*+ QB_NAME(src) NO_MERGE */ * FROM t2) z
ON (x.col_id1 = z.col_id1 AND x.col_id2 = z.col_id2)
WHEN MATCHED THEN UPDATE
SET x.col3 = z.col3;

With NO_MERGE applied, Oracle preserves the query block boundary, keeping your custom src query block name intact. You can then reference it in global hints at the top of the statement:

MERGE /*+ USE_HASH(x @src) */ ...

Solution 3: Use Global Hinting with System-Generated Names

If you cannot change the structure of the query, you can target the system-generated query block names directly. While you wanted to avoid names like SEL$8984BF49, they are highly reliable once the SQL structure is finalized.

To find the correct system-generated block names, generate the execution plan with the +ALIAS format:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL +ALIAS +HINT_REPORT'));

Once you identify the correct query block name from the plan (for example, SEL$8984BF49), you can write your hint at the top of the MERGE statement using the global hint syntax:

MERGE /*+ USE_HASH(@SEL$8984BF49 x z) */
INTO (SELECT * FROM t1) x
USING (SELECT * FROM t2) z...

Summary

  • Avoid inline views in MERGE statements where possible to prevent optimizer transformation conflicts.
  • Use the NO_MERGE hint alongside QB_NAME if you must use inline views and want to retain custom query block names.
  • Always verify your hints using DBMS_XPLAN.DISPLAY_CURSOR with the +HINT_REPORT option to ensure they are being actively used by the optimizer.