Search notes:
Oracle SQL Plan operation UNION-ALL
Create a few demonstration tables:
create table tq84_A (nm number, tx varchar2(10));
create table tq84_B (nm number, tx varchar2(10));
create table tq84_C (nm number, tx varchar2(10));
explain plan for
select nm, tx from tq84_A union all
select nm, tx from tq84_B union all
select nm, tx from tq84_c
;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- -------------------------------------
-- | Id | Operation | Name |
-- -------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | UNION-ALL | |
-- | 2 | TABLE ACCESS FULL| TQ84_A |
-- | 3 | TABLE ACCESS FULL| TQ84_B |
-- | 4 | TABLE ACCESS FULL| TQ84_C |
-- -------------------------------------
Almost the same thing, but using
union. Note the additional
SORT UNIQUE explain plan for
select nm, tx from tq84_A union
select nm, tx from tq84_B union
select nm, tx from tq84_c
;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- --------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | SORT UNIQUE | |
-- | 2 | UNION-ALL | |
-- | 3 | TABLE ACCESS FULL| TQ84_A |
-- | 4 | TABLE ACCESS FULL| TQ84_B |
-- | 5 | TABLE ACCESS FULL| TQ84_C |
-- --------------------------------------
Claning up:
drop table tq84_C;
drop table tq84_B;
drop table tq84_A;
See also
The
UNION-ALL plan operator might be used for the
OR expansion query transformation.
The CONCATENATION plan operator is functionally equivalent to UNION-ALL.