exists

Using EXISTS for join tables 


SELECT cr.name FROM currency cr WHERE EXISTS (SELECT 1 FROM event ev WHERE ev.currencyid = cr.currencyid)

PLAN_TABLE_OUTPUT
Plan hash value: 2107538316
----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |    36 |   756 |    58   (4)| 00:00:01 |
|*  1 |  HASH JOIN SEMI       |                |    36 |   756 |    58   (4)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | CURRENCY       |   182 |  3094 |     3   (0)| 00:00:01 |
|   3 |   INDEX FAST FULL SCAN| EVENT_CURR_IDX | 89526 |   349K|    54   (2)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EV"."CURRENCYID"="CR"."CURRENCYID")

Without EXISTS


SELECT DISTINCT  cr.name FROM currency cr,event ev WHERE ev.currencyid = cr.currencyid;

PLAN_TABLE_OUTPUT
Plan hash value: 1431156865
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |   182 |  3822 |    63  (12)| 00:00:01 |
|   1 |  HASH UNIQUE           |                |   182 |  3822 |    63  (12)| 00:00:01 |
|*  2 |   HASH JOIN            |                | 89526 |  1835K|    58   (4)| 00:00:01 |
|   3 |    TABLE ACCESS FULL   | CURRENCY       |   182 |  3094 |     3   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| EVENT_CURR_IDX | 89526 |   349K|    54   (2)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EV"."CURRENCYID"="CR"."CURRENCYID")