Oracle NOT IN or NOT EXISTS is very slow if I have a lot of rows in a table. So the solution which I have found is to use LEFT JOIN with IS NULL condition. Take a look on example below.
NOT IN vs. NOT EXISTS vs. LEFT JOIN and NULL
Example:
--Try:
SELECT COUNT (1)
FROM emp
WHERE id NOT IN ( SELECT emp_id
FROM salary
);
--Try:
SELECT COUNT (1)
FROM emp e
WHERE NOT EXISTS ( SELECT emp_id
FROM salary s
WHERE s.emp_id = e.id
);
--Use:
SELECT COUNT (e.id)
FROM emp e, salary s
WHERE e.id = s.emp_id(+)
AND s.emp_id IS NULL