Subquery in WHERE clause
Test data
create table tq84_A (id number, tx varchar2(3));
create table tq84_B (id_a number);
insert into tq84_A select level*37, 'X' from dual connect by level < 1000;
insert into tq84_B select level+91 from dual connect by level < 600;
begin
dbms_stats.gather_table_stats(user, 'tq84_A');
dbms_stats.gather_table_stats(user, 'tq84_B');
end;
/
Unnesting the subquery
With the given test data, Oracle's optimizer will unnest the subquery into the parent query which results in a semi-hash-join:
explain plan for
select
id,
tx
from
tq84_A
where
id in (select id_a from tq84_B);
select * from table(dbms_xplan.display(format=>'basic'));
--
-- ---------------------------------------
-- | Id | Operation | Name |
-- ---------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | HASH JOIN RIGHT SEMI| |
-- | 2 | TABLE ACCESS FULL | TQ84_B |
-- | 3 | TABLE ACCESS FULL | TQ84_A |
-- ---------------------------------------
Using the NO_UNNEST hint
However, when using the no_unnest hinst, both, the subquery and the query, are executed separately. The subquery is executed for each returned row:
explain plan for
select /*+ no_unnest(@subquery) */
id,
tx
from
tq84_A
where
id in (select /*+ qb_name(subquery) */ id_a from tq84_B);
select * from table(dbms_xplan.display(format=>'basic'));
--
-- -------------------------------------
-- | Id | Operation | Name |
-- -------------------------------------
-- | 0 | SELECT STATEMENT | |
-- |* 1 | FILTER | |
-- | 2 | TABLE ACCESS FULL| TQ84_A |
-- |* 3 | TABLE ACCESS FULL| TQ84_B |
-- -------------------------------------
--
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--
-- 1 - filter( EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SUBQUERY") */ 0
-- FROM "TQ84_B" "TQ84_B" WHERE "ID_A"=:B1))
-- 3 - filter("ID_A"=:B1)
Cleaning up
drop table tq84_A;
drop table tq84_B;
Scalar subquery in SELECT list
Test data
create table tq84_A (id number, txt varchar2(10));
create table tq84_b (id number, val number );
insert into tq84_A
select
level,
to_char(level, 'fmXX')
from
dual connect by level <= 255;
insert into tq84_B
select
mod(level, 255),
level + mod(level+7, 7) + mod(level+43, 43)
from
dual connect by level <= 1000;
begin
dbms_stats.gather_table_stats(user, 'tq84_A');
dbms_stats.gather_table_stats(user, 'tq84_B');
end;
/
Using scalar subquery
Again, without hinting, Oracle choses to join the table of the subquery to the table of the driving query:
explain plan for
select
a.id,
a.txt,
(select max(b.val) from tq84_B b where b.id = a.id) mx
from
tq84_A a
where
a.txt like '%C';
select * from table(dbms_xplan.display(format=>'basic'));
--
-- --------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | HASH GROUP BY | |
-- | 2 | HASH JOIN OUTER | |
-- | 3 | TABLE ACCESS FULL| TQ84_A |
-- | 4 | TABLE ACCESS FULL| TQ84_B |
-- --------------------------------------
Using NO_UNNEST hint
Using the no_unnest hint results in accessing both table separately:
explain plan for
select
a.id,
a.txt,
(select /*+ no_unnest */ max(b.val) from tq84_B b where b.id = a.id) mx
from
tq84_A a
where
a.txt like '%C';
select * from table(dbms_xplan.display(format=>'basic'));
--
-- -------------------------------------
-- | Id | Operation | Name |
-- -------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | SORT AGGREGATE | |
-- | 2 | TABLE ACCESS FULL| TQ84_B |
-- | 3 | TABLE ACCESS FULL | TQ84_A |
-- -------------------------------------
Cleaning up
drop table tq84_A;
drop table tq84_B;