Example
Create tables …
create table tq84_left (
id integer primary key,
text varchar2(3) not null
);
create table tq84_right (
id_left integer not null references tq84_left,
val number(5,2)
);
… fill them with some data …
insert into tq84_left values (1, 'foo');
insert into tq84_left values (2, 'bar');
insert into tq84_left values (3, 'baz');
insert into tq84_right values (1, 1.01);
insert into tq84_right values (1, 10.10);
insert into tq84_right values (1, 100.00);
insert into tq84_right values (2, 222.22);
insert into tq84_right values (3, 300.12);
insert into tq84_right values (3, 33.21);
commit;
… and then use cross apply to select from them.
Note that the join condition is formulated within the subquery, not after it:
select
l.text,
a.total
from
tq84_left l cross apply
(select
sum(r.val) total
from
tq84_right r
where
r.id_left = l.id
) a;
--
-- foo 111.11
-- bar 222.22
-- baz 333.33
explain plan for
select
l.text,
a.total
from
tq84_left l cross apply
(select
sum(r.val) total
from
tq84_right r
where
r.id_left = l.id
) a;
select * from table(dbms_xplan.display);
--
-- ----------------------------------------------------------------------------------------
-- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-- ----------------------------------------------------------------------------------------
-- | 0 | SELECT STATEMENT | | 6 | 282 | 7 (15)| 00:00:01 |
-- |* 1 | HASH JOIN OUTER | | 6 | 282 | 7 (15)| 00:00:01 |
-- | 2 | TABLE ACCESS FULL | TQ84_LEFT | 3 | 63 | 3 (0)| 00:00:01 |
-- | 3 | VIEW | VW_DCL_535DE542 | 6 | 156 | 4 (25)| 00:00:01 |
-- | 4 | HASH GROUP BY | | 6 | 156 | 4 (25)| 00:00:01 |
-- | 5 | TABLE ACCESS FULL| TQ84_RIGHT | 6 | 156 | 3 (0)| 00:00:01 |
-- ----------------------------------------------------------------------------------------
Show the execution plan of an SQL statement that uses left join rather than cross apply. Such an SQL statement produces the same result, but uses a different execution plan:
explain plan for
select
l.text,
sum(r.val) total
from
tq84_left l left join
tq84_right r on l.id = r.id_left
group by
l.text;
select * from table(dbms_xplan.display);
--
-- ----------------------------------------------------------------------------------
-- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-- ----------------------------------------------------------------------------------
-- | 0 | SELECT STATEMENT | | 6 | 282 | 7 (15)| 00:00:01 |
-- | 1 | HASH GROUP BY | | 6 | 282 | 7 (15)| 00:00:01 |
-- |* 2 | HASH JOIN OUTER | | 6 | 282 | 6 (0)| 00:00:01 |
-- | 3 | TABLE ACCESS FULL| TQ84_LEFT | 3 | 63 | 3 (0)| 00:00:01 |
-- | 4 | TABLE ACCESS FULL| TQ84_RIGHT | 6 | 156 | 3 (0)| 00:00:01 |
-- ----------------------------------------------------------------------------------