Search notes:

Oracle SQL Plan operation UNION-ALL

The plan operator UNION-ALL is used for both, queries using the set operators union all and union.

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;

Oracle SQL Plan operation UNION-ALL

See also