0

I've just become familiar with the hint "qb_name" (query block name) in Oracle and below you can see an example of using this hint in a query:

select
        /*+
                qb_name(main)
        */
        ord.id,
        ord.valuation,
        ord.status,
        (select /*+ qb_name(company) */ max(com.name)  from companies  com  where com.id  = ord.id_company) company,
        (select /*+ qb_name(product) */ max(prd1.name) from products   prd1 where prd1.id = orl.id_product) product,
        orl.quantity
from
        orders          ord,
        order_lines     orl
where
        ord.date_placed > trunc(sysdate) - 7
and     orl.id_ord = ord.id
and     orl.id_product in (
                select  /*+ qb_name(class) */
                        prd2.id
                from    products prd2
                where   prd2.class = 'Group25'
        )

I've searched about it but the articles weren't that practical so I've decided to ask about this hint here. My questions are :

  1. Does it have any significant effect on performance?
  2. Has anybody here used this hint in their query ? why ?
  3. In which circumstances we must use this hint?

Thanks in advance

2

It can be useful to provide your own names for query blocks in place of the SEL$1, SEL$2 etc generated by the optimiser. It may also be more reliable, if for example your top-level hints refer to sub-blocks by name, but the system-generated numbering later changes due to edits to the query, making nonsense of your /*+ full(e@sel$5) */ hint.

Unhinted query:

select * from emp e
where  e.deptno in
       ( select d.deptno
         from   dept d
         where  d.loc = 'DALLAS' )
and    e.job = 'ANALYST';

Plan includes:

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

   3 - SEL$5DA710D3 / E@SEL$1
   4 - SEL$5DA710D3 / D@SEL$2
   5 - SEL$5DA710D3 / D@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "E"@"SEL$1")
      INDEX(@"SEL$5DA710D3" "D"@"SEL$2" ("DEPT"."DEPTNO"))
      LEADING(@"SEL$5DA710D3" "E"@"SEL$1" "D"@"SEL$2")
      USE_NL(@"SEL$5DA710D3" "D"@"SEL$2")
      NLJ_BATCHING(@"SEL$5DA710D3" "D"@"SEL$2")
      END_OUTLINE_DATA
  */

Now if we use qblock_name to name the subquery block, and refer to it at the top level:

select /*+ index(d@deptsubq(dept.loc)) */ * from emp e
where  e.deptno in
       ( select /*+ qb_name(deptsubq) */ d.deptno
         from   dept d
         where  d.loc = 'DALLAS' )
and    e.job = 'ANALYST';

the same section of plan becomes:

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

   3 - SEL$F31E4C0D / E@SEL$1
   4 - SEL$F31E4C0D / D@DEPTSUBQ
   5 - SEL$F31E4C0D / D@DEPTSUBQ

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F31E4C0D")
      UNNEST(@"DEPTSUBQ")
      OUTLINE(@"SEL$1")
      OUTLINE(@"DEPTSUBQ")
      FULL(@"SEL$F31E4C0D" "E"@"SEL$1")
      INDEX(@"SEL$F31E4C0D" "D"@"DEPTSUBQ" ("DEPT"."LOC"))
      LEADING(@"SEL$F31E4C0D" "E"@"SEL$1" "D"@"DEPTSUBQ")
      USE_NL(@"SEL$F31E4C0D" "D"@"DEPTSUBQ")
      NLJ_BATCHING(@"SEL$F31E4C0D" "D"@"DEPTSUBQ")
      END_OUTLINE_DATA
  */

Also the hint report (19c) shows the named blocks:

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

   0 -  DEPTSUBQ
           -  qb_name(deptsubq)

   4 -  SEL$F31E4C0D / D@DEPTSUBQ
           -  index(d@deptsubq(dept.loc))

Although this is a trivial and contrived example, you can see how naming query blocks would make it easier to consolidate hints at the top level.

2

No performance impact at all, but it can make it easier to follow a complicated query plan and hint at specific subqueries (ie potential to allow you to make performance improvements easier).

I use it from time to time, mainly when I’m demoing execution plans as subqueries will retain query block names when you add bits.

There is no situation which requires you to use the hint.

1

The Documentation says it's for naming blocks of SQL.

Use the QB_NAME hint to define a name for a query block.

This seems to be more for the person that's tuning the SQL more than it's for the CBO.

Answers

  1. No
  2. I was unaware of it
  3. Use it when your query is very complex and you need help reading The Plan

I'll have to check. but, If the CTE name doesn't show in the Plan, that would be a good place to put it. (I'm usually using a minimum of 5 CTEs in one SQL statement.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.