Thanks for the question, Gordon.

Asked: August 31, 2015 - 2:08 pm UTC

Last updated: April 04, 2025 - 5:53 am UTC

Version: 12.10.2

Viewed 1000+ times

You Asked

Sometimes when I use a QB_NAME hint, when I later look at the execution plan, there is no record of that query block name.

Why is this the case ?

and Tom said...

The optimzier can rewrite your query in some situations. One effect is unnesting subqueries. If this happens, then your subquery is removed and the hint is not present.

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Gordon Cameron, August 31, 2015 - 2:23 pm UTC

OK, that would explain what I'm seeing. For example:

SQL> explain plan for
  2  select /*+ qb_name(q1) */ *
  3  from
  4    ( select /*+ qb_name(q2) */ *
  5      from emp
  6    );

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'BASIC +ALIAS'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3956160932

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| EMP  |
----------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$D8208F20 / EMP@Q2

Long Absence

Fahd Bahoo, September 01, 2015 - 8:14 am UTC

Hi tom,

Where where you Tom waited for you for 8 months. 
Such a long absence ?

Will you be replying now a day?

September 02, 2015 - 1:29 am UTC

AskTom is now back online.

See the asktom community page here: https://community.oracle.com/community/database/asktom

for all the details.

Thanks,
The AskTom team

Last Update Datetime(Home Page)

Shimmy, September 15, 2015 - 12:56 pm UTC

Why is the last update datetime on whole bunch of Q&A have yesterday's date(eg: "Last updated: September 15, 2015 - 2:18 am UTC") and when you click the link/detail, you don't see any new update from yesterday.

September 15, 2015 - 6:51 pm UTC

We just added the feature to display who answered the question. This required us to go back through the recent qs and update them to say whether it was Connor or me who answered. Hence the update dates :)

Thanks

Gordon Cameron, September 25, 2015 - 1:03 am UTC

Thanks for the clarification on query block names. Even with transformation, I still think they are quite useful.

September 25, 2015 - 1:04 am UTC

I Agree.

Execution plan with query block

Sasa Petkovic, March 29, 2025 - 8:41 am UTC

Hi Conor,
Please see following test case.
create table p (idp number primary key);
create table i (idi number, idp number);

create or replace view pi_v as
select /*+ qb_name (MAIN) */
p.*, i.idi
from p, i
where p.idp = i.idp(+);

explain plan for select * from pi_v;
select * from table(dbms_xplan.display (null, null, 'typical +alias'))

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 3 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 39 | 3 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | SYS_C0016181 | 1 | 13 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | I | 1 | 26 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$9654A89C
2 - SEL$9654A89C / P@MAIN
3 - SEL$9654A89C / I@MAIN

So far so good. Query block shows correctly. And if I want(for some reason) to force full table scan on table "P" I would do next:

explain plan for select /*+ full(@MAIN P) */ * from pi_v;
select * from table(dbms_xplan.display (null, null, 'typical +alias'))

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 39 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | P | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | I | 1 | 26 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$9654A89C
2 - SEL$9654A89C / P@MAIN
3 - SEL$9654A89C / I@MAIN

Excellent.
But let we see following example. Just to complicate little bit.

create or replace view pi_step1_v as
select /*+ qb_name(STEP1) */
*
from pi_v
where idp <> 0;

explain plan for select /*+ full(@MAIN P) */ * from pi_step1_v;
select * from table(dbms_xplan.display (null, null, 'typical +alias'))

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 39 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS STORAGE FULL| P | 1 | 13 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS STORAGE FULL| I | 1 | 26 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$AC61EC4E
2 - SEL$AC61EC4E / P@MAIN
3 - SEL$AC61EC4E / I@MAIN

Again nice. But if i do something as follows:

create or replace view pi_step2_v as
select /*+ qb_name(STEP2) */
m.*, s1.idp as idp_s1, s1.idi as idi_s1
from pi_v m, pi_step1_v s1
where m.idi = s1.idi;

explain plan for select * from pi_step2_v;
select * from table(dbms_xplan.display (null, null, 'typical +alias'))

I got following:

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 78 | 6 (0)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 65 | 5 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 39 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS STORAGE FULL| I | 1 | 26 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0016188 | 1 | 13 | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS STORAGE FULL | I | 1 | 26 | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C0016188 | 1 | 13 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$09BF7EB1
4 - SEL$09BF7EB1 / I@SEL$2
5 - SEL$09BF7EB1 / P@SEL$2
6 - SEL$09BF7EB1 / I@SEL$3
7 - SEL$09BF7EB1 / P@SEL$3

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("I"."IDI"="I"."IDI")
5 - access("P"."IDP"="I"."IDP")
6 - storage("I"."IDP"<>0)
filter("I"."IDP"<>0)
7 - access("P"."IDP"="I"."IDP")
filter("P"."IDP"<>0)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------

0 - SEL$2
U - qb_name (MAIN) / same QB_NAME hints for different query blocks

0 - SEL$3
U - qb_name (MAIN) / same QB_NAME hints for different query blocks

It now disappears query block name "MAIN" from execution plan ( which is probably expectable due to collision of the same qb_name hints from different blocks as stated).
Imagine that we want to achieve such optimization plan that tables "P" and "I" are always accessed via full table scan no matter from which view they are "comming".
I would do next but seems that this does not work.

explain plan for select /*+ full(@SEL$2 O) full(@SEL$3 O) */* from pi_step2_v;
select * from table(dbms_xplan.display (null, null, 'typical +alias'));

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 78 | 6 (0)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 65 | 5 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 39 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS STORAGE FULL| I | 1 | 26 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0016188 | 1 | 13 | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS STORAGE FULL | I | 1 | 26 | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C0016188 | 1 | 13 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$09BF7EB1
4 - SEL$09BF7EB1 / I@SEL$2
5 - SEL$09BF7EB1 / P@SEL$2
6 - SEL$09BF7EB1 / I@SEL$3
7 - SEL$09BF7EB1 / P@SEL$3

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("I"."IDI"="I"."IDI")
5 - access("P"."IDP"="I"."IDP")
6 - storage("I"."IDP"<>0)
filter("I"."IDP"<>0)
7 - access("P"."IDP"="I"."IDP")
filter("P"."IDP"<>0)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (2), N - Unresolved (2))
---------------------------------------------------------------------------
0 - SEL$2
N - full(@SEL$2 O)
U - qb_name (MAIN) / same QB_NAME hints for different query blocks

0 - SEL$3
N - full(@SEL$3 O)
U - qb_name (MAIN) / same QB_NAME hints for different query blocks

Is there are any way of achieveing this?
Best
Sasa

April 04, 2025 - 5:53 am UTC

If you run

variable c clob
exec dbms_utility.expand_sql_text('select * from pi_step2_v',:c)

you can see where the issue is. Your final query becomes

SELECT "A1"."IDP" "IDP","A1"."IDI" "IDI","A1"."IDP_S1" "IDP_S1","A1"."IDI_S1" "IDI_S1" 
FROM  
(SELECT /*+ QB_NAME ("STEP2") */ "A3"."IDP" "IDP","A3"."IDI" "IDI","A2"."IDP" "IDP_S1","A2"."IDI" "IDI_S1" 
 FROM  
   (SELECT /*+ QB_NAME ("MAIN") */ "A5"."IDP" "IDP","A4"."IDI" "IDI" 
    FROM "SCOTT"."TP" "A5","SCOTT"."TI" "A4" 
    WHERE "A5"."IDP"="A4"."IDP"(+)) "A3", 
     (SELECT /*+ QB_NAME ("STEP1") */ "A6"."IDP" "IDP","A6"."IDI" "IDI" 
      FROM  
       (SELECT /*+ QB_NAME ("MAIN") */ "A8"."IDP" "IDP","A7"."IDI" "IDI" 
        FROM "SCOTT"."TP" "A8","SCOTT"."TI" "A7" 
        WHERE "A8"."IDP"="A7"."IDP"(+)) "A6" 
     WHERE "A6"."IDP"<>0) "A2" 
WHERE "A3"."IDI"="A2"."IDI") "A1"

hence the duplicate block names.

There is a fairly extensive discussion on Jonathan Lewis's post on this

https://jonathanlewis.wordpress.com/2022/07/26/hinting-5/

which talks about the challenges of hinting when the optimizer is doing transformations.

In general, you would generate the outline (with the system generated block names) and then use them to force a hint deeper into the plan.

Execution plan with query block

Sasa Petkovic, March 29, 2025 - 8:59 am UTC

Sorry...just realised have a "bug" in the latest query.
Using full(.. O) while it should be "P".

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thanks

Long Absence

Last Update Datetime(Home Page)

Thanks

Execution plan with query block

Execution plan with query block

More to Explore

Live SQL

Dev Gym

Query block names disappear

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thanks

Long Absence

Last Update Datetime(Home Page)

Thanks

Execution plan with query block

Execution plan with query block

More to Explore

Live SQL

Dev Gym