2 slimdave

This is real query (or near real, because view based on this query).
This query use MINUS (it's like INTERSECT).
U can see execution plan if u don't belive that this explains the difference.

Code:
select a.period, center, ent, 1 as source_id
from dtax_model.TM_CC_ENT_LKUP  a
   , dtax_mlt.MLT_ENT           b 
WHERE a.period = b.period 
  AND a.ent_id = b.ENT_ID 
  AND a.active ='Y' 
union all
( 
        select a.period, center, ent, 2
        from dtax_mlt.MLT_CC_ENT_LKUP   a
           , dtax_mlt.MLT_ENT           b 
        WHERE a.period = b.period 
          AND a.ent_id = b.ENT_ID 
   minus
        select a.period, center, ent, 2
        from dtax_model.TM_CC_ENT_LKUP  a
           , dtax_mlt.MLT_ENT           b 
        WHERE a.period = b.period 
          AND a.ent_id = b.ENT_ID 
          AND a.active = 'Y' 
)
;
Plan :
Code:
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=52 Bytes=884)
   1    0   VIEW OF 'V_TM_CC_ENT_LKUP_NEW2' (Cost=9 Card=52 Bytes=884)
   2    1     UNION-ALL
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TM_CC_ENT_LKUP' (Cost=2 Card=10334 Bytes=175678)
   4    3         NESTED LOOPS (Cost=3 Card=13 Bytes=390)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'MLT_ENT' (Cost=2 Card=1 Bytes=13)
   6    5             INDEX (RANGE SCAN) OF 'IDX_ENT_PERIOD' (NON-UNIQUE) (Cost=1 Card=1)
   7    4           INDEX (RANGE SCAN) OF 'IDX_1_ENT_CC' (NON-UNIQUE) (Cost=1 Card=10334)
   8    2       MINUS
   9    8         SORT (UNIQUE)
  10    9           TABLE ACCESS (BY INDEX ROWID) OF 'MLT_CC_ENT_LKUP' (Cost=1 Card=40900 Bytes=613500)
  11   10             NESTED LOOPS (Cost=3 Card=26 Bytes=728)
  12   11               TABLE ACCESS (BY INDEX ROWID) OF 'MLT_ENT' (Cost=2 Card=1 Bytes=13)
  13   12                 INDEX (RANGE SCAN) OF 'IDX_ENT_PERIOD' (NON-UNIQUE) (Cost=1 Card=1)
  14   11               INDEX (RANGE SCAN) OF 'IDX_ENT' (NON-UNIQUE)
  15    8         SORT (UNIQUE)
  16   15           TABLE ACCESS (BY INDEX ROWID) OF 'TM_CC_ENT_LKUP'(Cost=2 Card=10334 Bytes=175678)
  17   16             NESTED LOOPS (Cost=3 Card=13 Bytes=390)
  18   17               TABLE ACCESS (BY INDEX ROWID) OF 'MLT_ENT' (Cost=2 Card=1 Bytes=13)
  19   18                 INDEX (RANGE SCAN) OF 'IDX_ENT_PERIOD' (NON-UNIQUE) (Cost=1 Card=1)
  20   17               INDEX (RANGE SCAN) OF 'IDX_1_ENT_CC' (NON-UNIQUE) (Cost=1 Card=10334)
Steps :
9 SORT (UNIQUE)
15 SORT (UNIQUE)
show extra sort operations.