How to optimize a query for a large amount of data?

Question

Oracle

There is a query that needs to be optimized for execution on a large amount of data.

SELECT PID FROM auto WHERE 
PID NOT IN (SELECT DISTINCT auto_id FROM auto_in)
AND fk_id_client IN
(SELECT PID FROM client WHERE id_client IN
(SELECT id_client FROM client WHERE F_TYPE = 'BASE' GROUP BY id_client HAVING COUNT(*) >= 2))

The easiest way, probably, is a cycle. At first I wanted to use the while loop, but I already doubt it.

Answer 1

Optimizing queries "by photo" is hopeless.
First of all, you need the same amount of data on which you can check.
But the general advice is to replace IN / NOT IN with joins / anti-joins.

Answer 2

get away from nested queries to start with