Union with distinct

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



Union without distinct

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



Interesct with distinct

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



Intersect without distinct

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



Minus with distinct

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



Minus without distinct

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




8 rows deleted.


Explained.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

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

Note: cpu costing is off

14 rows selected.


7 rows deleted.


Explained.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

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

Note: cpu costing is off

15 rows selected.


8 rows deleted.


Explained.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

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

Note: cpu costing is off

15 rows selected.

