MINUS has two child row-sources of which the first typically is a SORT UNIQUE and the second a SORT UNIQUE or a TABLE ACCESS BY INDEX ROWID BATCHED (but I've seen other operations also). create table tq84_X (txt varchar2(10));
create table tq84_Y (txt varchar2(10));
begin
insert into tq84_X values ('three');
insert into tq84_X values ('two' );
insert into tq84_X values ('three');
insert into tq84_X values ('two' );
insert into tq84_X values ('one' );
insert into tq84_Y values ('two' );
insert into tq84_Y values ('xxx' );
insert into tq84_Y values ( null );
end;
/
explain plan for
select txt from tq84_X minus
select txt from tq84_Y;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- --------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | MINUS | |
-- | 2 | SORT UNIQUE | |
-- | 3 | TABLE ACCESS FULL| TQ84_X |
-- | 4 | SORT UNIQUE | |
-- | 5 | TABLE ACCESS FULL| TQ84_Y |
-- --------------------------------------
drop table tq84_X;
drop table tq84_Y;