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