Search

OakieTags

Who's online

There are currently 0 users and 37 guests online.

Recent comments

Affiliations

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 = 3Now 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.