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")

The shared pool

The shared pool is the portion of SGA contains the following main parts:

1. The Library Cache includes sharable parts of SQL statments, PL/SQL procedures and packages
2. The Data Dictionarry is a collection of database tables containing reference information about the database.
3. The Result Cache is used for storing results of SQL statments and PL/SQL procedures.
4. Lock Structures

Data buffer cache

The database buffer cache is part of SGA that holds a copies of data blocks that are read from data files.This cache is managed by LRU (Last recently used algoritm).  The first time an Ora DB Server process needs for some portion of data it tries to find them in the Data buffer cashe. If data are there Ora Process reads blocks from memory.

Difference between Oracle Database and Oracle Database Instance

An Oracle Database Server contains of Oracle Database and one or more Oracle Database instances.An instance contains with of memory structure and baskground process.Every tyme when instance is  started a shared memory is called SGA is allocated starting a background process.When users connect to an Oracle Database server, they are connected to an Oracle Database instance.