What bberg has written is totaly correct.

I just want to add one thing. Pando, you are reading your explain plan wrong. In fact, what is happening during the execution of the query is:

Step1: Index DEPT_DEPTNO_PK is searched for a unique value of DEPTNO=2
Step2: The corresponding row from table DEPT is read directly by rowid obtained in step 1
Step3: Table EMP is full-scaned to find matching rows to the DEPT row found in step 2.

In this case the so-called driving table is table DEPT. In case you were looking for more than one department, then a RANGE SCAN would be performed on index in step 1, and for each row obtained from that step it would repeat steps 2 and 3.

As allready mentioned, you could persuade the optimizer to use index on EMP. Either suggest this with proper hint in a query (/*+INDEX*/ or /*+RULE*/ will do it) or change optimizer mode to RULE (either on the system or session level) or delete statistics on both tables involved (thus implicitly forcing RULE mode). Hovewer it is almost certain that the response time will be worse if accessing the EMP table via index in this case where whole table is stored in a couple of blocks that can be read in a single I/O operation.

This is one example where cost based optimizer is probably chosing more clever execution plan than the one user is trying to achive. This, of course, is not allways the case, but with each version Oracle optimizer is becoming more "clever" and reliable, provided that you feed him with accurate statistics.

HTH,