Wednesday, February 20, 2013

QB_NAME hint query block name length limitation

Oracle 10g introduced the QB_NAME hint that can come handy in case hints need to be applied to more complex statements, in particular when possibly multiple layers of views / subqueries are involved.

Jonathan Lewis has a older blog post that describes more details.

Just in case you wonder why sometimes apparently the QB_NAME hint - along with all other hints that refer to the assigned query block name - seems to be ignored: One possible reason is that it looks like there is an undocumented length limitation of the query block names that can be assigned - 20 characters seem to be the maximum possible (I haven't checked the effect of multi-byte database character sets).

Consider this simple example:

drop table t1;

purge table t1;

create table t1
as
select
        rownum as id
      , rpad('x', 100) as filler
from
        dual
connect by
        level <= 100
;

exec dbms_stats.gather_table_stats(null, 't1')

By default Oracle 11.2.0.1 unnests the correlated subquery and runs an anti-join for the following simple query:

select 
       *
from
       t1
where
       not exists
       (
         select 
                null
         from
                t1 t1_inner
         where
                t1_inner.id = t1.id
       );

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN ANTI    |      |
|   2 |   TABLE ACCESS FULL| T1   |
|   3 |   TABLE ACCESS FULL| T1   |
-----------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / T1@SEL$1
   3 - SEL$5DA710D3 / T1_INNER@SEL$2

Notice the aliases and query block names assigned, like SEL$1, SEL$2 etc.

Using the QB_NAME hint the NO_UNNEST hint can be applied to the correlated subquery from the outer query block:

select /*+ no_unnest(@nested_query) */
       *
from
       t1
where
       not exists
       (
         select /*+ qb_name(nested_query) */
                null
         from
                t1 t1_inner
         where
                t1_inner.id = t1.id
       );

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  FILTER            |      |
|   2 |   TABLE ACCESS FULL| T1   |
|*  3 |   TABLE ACCESS FULL| T1   |
-----------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1       
   2 - SEL$1        / T1@SEL$1
   3 - NESTED_QUERY / T1_INNER@NESTED_QUERY

The correlated subquery is now executed using a FILTER operator as requested. Notice the aliases and query block names, in particular for the instance of T1 at operation id = 3

Now if I repeat the same query, but use a longer query block name, the hints are effectively ignored and the default unnesting takes place again:

select /*+ no_unnest(@nested_query_longer_name) */
       *
from
       t1
where
       not exists
       (
         select /*+ qb_name(nested_query_longer_name) */
                null
         from
                t1 t1_inner
         where
                t1_inner.id = t1.id
       );

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN ANTI    |      |
|   2 |   TABLE ACCESS FULL| T1   |
|   3 |   TABLE ACCESS FULL| T1   |
-----------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / T1@SEL$1
   3 - SEL$5DA710D3 / T1_INNER@SEL$2

Notice how the default aliases and query block names get used although explicitly hinted.

It is probably rather uncommon to use such lengthy query block names, nevertheless it can be puzzling when hitting such an undocumented limitation.

5 comments:

  1. Hi Randolf,

    once again I think that it would be nice if there was an (dbms_xplan) option to show the hints the CBO did consider (to distinguish them from syntactical errors).

    Regards

    Martin

    ReplyDelete
  2. Hi Martin,

    that would be a nice idea, yes.

    Although it would still leave one puzzled in such a case here where no "obvious" syntactical problem can be identified.

    The "hint" section in the 10053 optimizer trace file provides such information (in a rather obscure way), so it probably wouldn't be too hard to come up with an official interface.

    Randolf

    ReplyDelete
  3. Good find, thanks! This probably explains the issue I had a few months ago where my QB_NAME directive was seemingly ignored. I didn't have time to think about it much, ranked it as "must be some bug in this version" and moved on. But I did use a long enough QB name there.

    ReplyDelete
  4. I was initially confused by your article, because we in our company had noticed a while ago that QB_NAME identifiers can be chosen up to 30 characters long, or otherwise they will invalidate the hints following them.

    Example:

    select /*+ qb_name(a23456789012345678901234567890) index(s) 30 char */ * from sys.source$ s

    --> leads to in index-full scan

    select /*+ qb_name(a234567890123456789012345678901) index(s) 31 char */ * from sys.source$ s

    --> table full scan; index(s) hint is ignored

    (30 characters also fits in well with the fact that these identifiers are also not supposed to contain special characters which are reserved word -- like ; . - + * -- just like general Oracle identifiers.)

    Now you claim that 25 characters is the limit??! But of course in a slightly different context:
    NO_UNNEST-hints become invalid if the QB_NAME identifier is longer than 25 characters.

    Makes you wonder whether there are other hints in which you can specify QB_NAMEs with yet other length limits than 25 and 30.

    ReplyDelete
  5. Hi Thomas,

    thanks for your comment.

    I believe there are two things to consider:

    1. I've shown in my post that the QB_NAME query block name gets ignored if the length of the name is longer than 20 characters (not 25).

    Note that this seems to be apply in general, and not only to the specific case of unnesting subqueries.

    2. You describe the issue of hints that are "invalid" to Oracle lead to hints that follow such an invalid hint to be ignored. That does apply in general to all hint directives that Oracle classifies as invalid.

    Charles Hooper has a lot of interesting blog posts on the topic of "invalid" hints, and MOS Note 826893.1 also describes some details.

    Using a QB_NAME hint with a query block name that is longer than 30 characters seems to make this hint to be classified as invalid and hence any hints following will be ignored, too.

    You can see this behaviour also with other "invalid" hints, so it is not limited to QB_NAME.

    The point that you would need to clarify in my opinion:

    When you're using such a QB_NAME hint that is "valid" (no longer than 30 chars in the query block name, so hints following will still be considered) but longer than 20 chars, can you use that query block name from outside your query block to apply hints to that query block, and what does your "Query Block Name" section look like?

    This is something that is not obvious from your posted example, because you specify the INDEX hint on the inner query block level, not outside as I did in my example.

    Hint: Look at the "Query Block Name" and "Outline" section of DBMS_XPLAN.DISPLAY* when using this:

    select /*+ qb_name(a2345678901234567890) index(s) 20 char */ * from sys.source$ s;

    versus this:

    select /*+ qb_name(a23456789012345678901) index(s) 21 char */ * from sys.source$ s;

    and you'll notice the difference I describe in my post.

    Randolf

    ReplyDelete