Union with distinct

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=30 Card=35 Bytes=455)
   1    0   SORT (UNIQUE) (Cost=30 Card=35 Bytes=455)
   2    1     UNION-ALL
   3    2       TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=6 Card=2500 Bytes=32500)
   4    2       TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=6 Card=2000 Bytes=26000)



Union without distinct

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=37 Card=4500 Bytes=58500)
   1    0   SORT (UNIQUE) (Cost=37 Card=4500 Bytes=58500)
   2    1     UNION-ALL
   3    2       TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=6 Card=2500 Bytes=32500)
   4    2       TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=6 Card=2000 Bytes=26000)



Interesct with distinct

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=30 Card=15 Bytes=455)
   1    0   INTERSECTION
   2    1     SORT (UNIQUE) (Cost=15 Card=20 Bytes=260)
   3    2       TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=6 Card=2500 Bytes=32500)
   4    1     SORT (UNIQUE) (Cost=15 Card=15 Bytes=195)
   5    4       TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=6 Card=2000 Bytes=26000)



Intersect without distinct

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=37 Card=2000 Bytes=58500)
   1    0   INTERSECTION
   2    1     SORT (UNIQUE) (Cost=19 Card=2500 Bytes=32500)
   3    2       TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=6 Card=2500 Bytes=32500)
   4    1     SORT (UNIQUE) (Cost=18 Card=2000 Bytes=26000)
   5    4       TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=6 Card=2000 Bytes=26000)



Minus with distinct

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=30 Card=20 Bytes=455)
   1    0   MINUS
   2    1     SORT (UNIQUE) (Cost=15 Card=20 Bytes=260)
   3    2       TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=6 Card=2500 Bytes=32500)
   4    1     SORT (UNIQUE) (Cost=15 Card=15 Bytes=195)
   5    4       TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=6 Card=2000 Bytes=26000)



Minus without distinct

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=37 Card=2500 Bytes=58500)
   1    0   MINUS
   2    1     SORT (UNIQUE) (Cost=19 Card=2500 Bytes=32500)
   3    2       TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=6 Card=2500 Bytes=32500)
   4    1     SORT (UNIQUE) (Cost=18 Card=2000 Bytes=26000)
   5    4       TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=6 Card=2000 Bytes=26000)




0 rows deleted.


Explained.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 99469579

---------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |      |  4500 |   123K|    71 |
|   1 |  LOAD AS SELECT        |      |       |       |       |
|   2 |   SORT UNIQUE          |      |  4500 |   123K|    67 |
|   3 |    VIEW                |      |  4500 |   123K|    40 |
|   4 |     SORT UNIQUE        |      |  4500 | 58500 |    40 |
|   5 |      UNION-ALL         |      |       |       |       |
|   6 |       TABLE ACCESS FULL| T1   |  2500 | 32500 |     6 |
|   7 |       TABLE ACCESS FULL| T2   |  2000 | 26000 |     6 |
---------------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)

18 rows selected.


8 rows deleted.


Explained.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3577302533

---------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |      |  2000 | 56000 |    57 |
|   1 |  LOAD AS SELECT        |      |       |       |       |
|   2 |   SORT UNIQUE          |      |  2000 | 56000 |    55 |
|   3 |    VIEW                |      |  2000 | 56000 |    40 |
|   4 |     INTERSECTION       |      |       |       |       |
|   5 |      SORT UNIQUE       |      |  2500 | 32500 |    19 |
|   6 |       TABLE ACCESS FULL| T1   |  2500 | 32500 |     6 |
|   7 |      SORT UNIQUE       |      |  2000 | 26000 |    18 |
|   8 |       TABLE ACCESS FULL| T2   |  2000 | 26000 |     6 |
---------------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)

19 rows selected.


9 rows deleted.


Explained.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2045982360

---------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |      |  2500 | 70000 |    59 |
|   1 |  LOAD AS SELECT        |      |       |       |       |
|   2 |   SORT UNIQUE          |      |  2500 | 70000 |    57 |
|   3 |    VIEW                |      |  2500 | 70000 |    40 |
|   4 |     MINUS              |      |       |       |       |
|   5 |      SORT UNIQUE       |      |  2500 | 32500 |    19 |
|   6 |       TABLE ACCESS FULL| T1   |  2500 | 32500 |     6 |
|   7 |      SORT UNIQUE       |      |  2000 | 26000 |    18 |
|   8 |       TABLE ACCESS FULL| T2   |  2000 | 26000 |     6 |
---------------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)

19 rows selected.

