The following query took 25 senconds to run, the first select returned 1645 records and took 406 msec, the second select returned 0 record and took 31 msec. I had tried to use the UNION ALL but the response time seems inconsistent.



SELECT S.SUPPLIER_ID AS "SUPPLIER_ID"
FROM COMPANY_DEFAULT_PERMISSIONS cdp, USERNAME u, SUPPLIER S
WHERE u.username_id = 117120
AND u.company_id = cdp.company_id
AND U.COMPANY_ID = S.COMPANY_ID
AND S.SUPPLIER_ID NOT IN (SELECT SUPPLIER_ID
FROM SUPPLIER_SPM_ACL SSA
WHERE SSA.SUPPLIER_ID = S.SUPPLIER_ID AND (SSA.username_id = 117120 OR SSA.user_group_id IN (SELECT user_group_id FROM USERNAME_GROUP_MAP WHERE username_id = 117120)))
AND cdp.can_read = 1
AND cdp.object_type_id = 9
UNION
SELECT S.SUPPLIER_ID AS "SUPPLIER_ID"
FROM SUPPLIER_SPM_ACL SSA, USERNAME u, SUPPLIER S
WHERE u.username_id = 117120
AND u.username_id = SSA.username_id
AND U.COMPANY_ID = S.COMPANY_ID
AND S.SUPPLIER_ID = SSA.SUPPLIER_ID
AND SSA.can_read = 1