After a statement has been parsed, the parsed query (which represents the statement's query blocks) is passed to the
query transformer which tries to transform these query blocks into a more optimal execution plan, using methods such as
Showing query block names in an execution plan
The following example tries to demonstrate how query block names show up in an
explained SQL statement.
Create two table to be used for the query:
create table tq84_outer (
id number,
txt varchar2(20),
foo varchar2(20)
);
create table tq84_inner (
id number,
txt varchar2(20)
);
Use the
qb_name hints to specifically name a
subquery:
explain plan for
select /*+ qb_name(outer_query) */
id,
txt
from
tq84_outer one where
one.id = (select /*+ qb_name(subquery) */
max(two.id)
from
tq84_inner two
where
two.txt = one.txt
);
select
lpad('| ', (pln.depth-1) * 2, '| ') || pln.operation || case when pln.options is not null then ' (' || pln.options || ')' end op,
pln.object_owner || nvl2(pln.object_owner, '.', '') || pln.object_name object_owner_name,
pln.object_alias,
pln.qblock_name
from
plan_table pln where plan_id = (select max(plan_id) from plan_table)
order by
pln.id
;
The plan is:
OP OBJECT_OWNER_NAME OBJECT_ALIAS QBLOCK_NAME
----------------------- ----------------- ------------------ -----------
SELECT STATEMENT
FILTER OUTER_QUERY
| TABLE ACCESS (FULL) RENE.TQ84_OUTER "ONE"@"OUTER_QUERY" OUTER_QUERY
| SORT (AGGREGATE) SUBQUERY
| | TABLE ACCESS (FULL) RENE.TQ84_INNER "TWO"@"SUBQUERY" SUBQUERY
TODO: Try the same thing with an index:
create index tq84_outer_ix on tq84_outer(id);