Intersect and exists performance Oracle11g

Question

I would like to know whether INTERSECT or EXISTS have better performance in Oracle 11g. Consider I have the below two tables.

Student_Master

STUDENT_ID   NAME
----------  ------
STUD01       ALEX
STUD02       JAMES
STUD03       HANS

Student_Status

STUDENT_ID   STATUS
----------  ------
STUD01       Fail
STUD02       Pass
STUD03       Pass

Which of the below query will perform better considering that the table Student_Status will have more number of records compared to the table Student_Master.

SELECT STUDENT_ID FROM Student_Master
INTERSECT
SELECT STUDENT_ID FROM Student_Status

SELECT STUDENT_ID FROM Student_Master M
WHERE EXISTS
(SELECT STUDENT_ID FROM Student_Status S WHERE M.STUDENT_ID=S.STUDENT_ID)

Answer 1

A quick test would suggest the EXISTS option...

SELECT STUDENT_ID FROM Student_Master INTERSECT SELECT STUDENT_ID FROM
Student_Status

Plan hash value: 416197223

--------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |       |       |     6 (100)|          |
|   1 |  INTERSECTION         |                  |       |       |              |            |
|   2 |   SORT UNIQUE         |                  |     3 |    36 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| STUDENT_MASTER |     3 |    36 |     2   (0)| 00:00:01 |
|   4 |   SORT UNIQUE         |                  |     3 |    36 |     3  (34)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| STUDENT_STATUS |     3 |    36 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

-

SELECT STUDENT_ID FROM Student_Master M WHERE EXISTS (SELECT STUDENT_ID
FROM Student_Status S WHERE M.STUDENT_ID=S.STUDENT_ID)

Plan hash value: 361045672

-------------------------------------------------------------------------------------
| Id  | Operation      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |       |       |     4 (100)|      |
|*  1 |  HASH JOIN SEMI    |            |     3 |    72 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| STUDENT_MASTER |     3 |    36 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| STUDENT_STATUS |     3 |    36 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------