Query Performance improvement

Hi Team,
The query below is taking too much time when we load 7000-8000 records through the datafile (generic load program).
The query-related program is taking nearly 3 hours.
We tried NOT EXITS condition,
But based on our requirements, we are not getting the exact results.

SELECT
hp1.party_name,
hp.party_id,
hp1.party_id link_id,
hr.relationship_id,
(
SELECT
max(subject_id)
FROM
hz_relationships a,
hz_parties b
WHERE
object_id = hp1.party_id
AND b.party_id = a.subject_id
) cust_id,
hcar.cust_account_id,
hcar.cust_account_role_id,
hcar.object_version_number
FROM