create table tq84_A (txt varchar2(10), num number);
create table tq84_X (txt varchar2(10), val number);
create table tq84_Y (txt varchar2(10), val number);
create table tq84_Z (txt varchar2(10), val number);
explain plan for
select
a.num,
a.txt,
(select x.val from tq84_X x where x.txt = a.txt) val_x,
(select y.val from tq84_Y y where y.txt = a.txt) val_y,
(select z.val from tq84_Z z where z.txt = a.txt) val_z
from
tq84_A a
;
The (scalar) subqueries can be identifed in a displayed execution plan by the query blocks. In order to display query block names with dbms_xplan.display, the specified alias or format=>'all' is needed:
drop table tq84_X;
drop table tq84_Y;
drop table tq84_Z;
drop table tq84_A;
CASE WHEN … END expressions
create table tq84_A (id number, txt varchar2(10));
create table tq84_B (id number, txt varchar2(10));
create table tq84_C (id number, txt varchar2(10));
create table tq84_X (id number, txt varchar2(10));
create table tq84_Y (id number, txt varchar2(10));
create table tq84_Z (id number, txt varchar2(10));
create table tq84_main (
num number,
dat date
);
explain plan for
select
m.num,
m.dat,
case
when m.num = 1 then
(select txt from tq84_A i where i.id = m.num)
when m.num = 2 then
(select txt from tq84_B i where i.id = m.num)
when m.num = 3 then
(select txt from tq84_C i where i.id = m.num)
else
greatest (
(select txt from tq84_X i where i.id = m.num),
(select txt from tq84_Y i where i.id = m.num),
(select txt from tq84_Z i where i.id = m.num)
)
end c
from
tq84_main m;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- --------------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | TABLE ACCESS FULL | TQ84_A |
-- | 2 | TABLE ACCESS FULL | TQ84_B |
-- | 3 | TABLE ACCESS FULL | TQ84_C |
-- | 4 | TABLE ACCESS FULL | TQ84_X |
-- | 5 | TABLE ACCESS FULL | TQ84_Y |
-- | 6 | TABLE ACCESS FULL| TQ84_Z |
-- | 7 | TABLE ACCESS FULL | TQ84_MAIN |
-- --------------------------------------------
drop table tq84_main;
drop table tq84_Z;
drop table tq84_Y;
drop table tq84_X;
drop table tq84_C;
drop table tq84_B;
drop table tq84_A;