I’m Using Oracle 11.

I desperately need help with a query that is running too slow - it's a very simple problem. I have a table with 16 million rows and an index (let's call it the employee table with an index on department). I need to select all the employees whose departments are located in the uk. I achieve this by selecting all the department numbers from departments where location = 'UK' in a sub select then plug this into the main query as follows:

SELECT *
FROM employees
WHERE department IN (SELECT department from departments where location = 'UK');

It takes ages, 25 seconds or more, the explain plan shows its doing a full table scan on emplyees. I need it to use the index. The sub query is instant and returns only 5 rows. If I explicitly put the 5 numbers in the IN clause the query uses the index and executes in 0.04 seconds. See below:

SELECT *
FROM employees
WHERE department IN (1,2,3,4,5);

This is so frustrating, please can anyone help, I need it to use the subquery once and then use the index on the main table.

Many thanks.