Prepare two demonstration tables, create a primary-foreign key relationship between then, insert a bit of sample data and gather optimizer statistics
create table tq84_Q(id, txt)
as
select
level,
to_char(level, 'fmxxxx')
from
dual connect by level <= 1000;
create table tq84_V(q, txt, xyz)
as
select
1+mod(level, 1000),
to_char(level, 'fmxxxx'),
rpad('*', 250, '*')
from
dual connect by level <= 10000;
alter table tq84_Q add constraint tq84_Q_pk primary key (id);
alter table tq84_V add constraint tq84_V_fk foreign key (q ) references tq84_Q;
begin
dbms_stats.gather_table_stats(user, 'tq84_Q');
dbms_stats.gather_table_stats(user, 'tq84_V');
end;
/
Show the execution plan for a statement that involves both tables. We use the alias modifier in the format parameter of the dbms_xplan.display call to show query block names. There is only one query block: SEL$F5BB74E1:
explain plan for
select
q.id,
v.txt
from
tq84_Q q,
(select
q,
txt
from
tq84_V
) v
where
q.id = v.q and
q.txt = '1af'
;
select * from table(dbms_xplan.display(format => 'basic alias'));
--
-- -------------------------------------
-- | Id | Operation | Name |
-- -------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | HASH JOIN | |
-- | 2 | TABLE ACCESS FULL| TQ84_Q |
-- | 3 | TABLE ACCESS FULL| TQ84_V |
-- -------------------------------------
--
-- Query Block Name / Object Alias (identified by operation id):
-- -------------------------------------------------------------
--
-- 1 - SEL$F5BB74E1
-- 2 - SEL$F5BB74E1 / Q@SEL$1
-- 3 - SEL$F5BB74E1 / TQ84_V@SEL$2
Same query, but using the no_merge hint. Note that we now have two query blocks: SEL$1 and SEL$2. This is because the subquery is not merged into the outer query anymore:
explain plan for
select /*+ no_merge(v) */
q.id,
v.txt
from
tq84_Q q,
(select
q,
txt
from
tq84_V
) v
where
q.id = v.q and
q.txt = '1af'
;
select * from table(dbms_xplan.display(format => 'basic alias'));
--
-- --------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | HASH JOIN | |
-- | 2 | TABLE ACCESS FULL | TQ84_Q |
-- | 3 | VIEW | |
-- | 4 | TABLE ACCESS FULL| TQ84_V |
-- --------------------------------------
--
-- Query Block Name / Object Alias (identified by operation id):
-- -------------------------------------------------------------
--
-- 1 - SEL$1
-- 2 - SEL$1 / Q@SEL$1
-- 3 - SEL$2 / V@SEL$1
-- 4 - SEL$2 / TQ84_V@SEL$2
It's also possible to specify the no_merge hint (without arguments) in the inner query, having the same effect:
explain plan for
select
q.id,
v.txt
from
tq84_Q q,
(select /*+ no_merge */
q,
txt
from
tq84_V
) v
where
q.id = v.q and
q.txt = '1af'
;
select * from table(dbms_xplan.display(format => 'basic alias'));
--
-- --------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | HASH JOIN | |
-- | 2 | TABLE ACCESS FULL | TQ84_Q |
-- | 3 | VIEW | |
-- | 4 | TABLE ACCESS FULL| TQ84_V |
-- --------------------------------------
--
-- Query Block Name / Object Alias (identified by operation id):
-- -------------------------------------------------------------
--
-- 1 - SEL$1
-- 2 - SEL$1 / Q@SEL$1
-- 3 - SEL$2 / V@SEL$1
-- 4 - SEL$2 / TQ84_V@SEL$2
Cleaning up:
drop table tq84_V;
drop table tq84_Q;
See also
The merge hint is quite different from the use_merge hint.