[SQL] Slow query in Oracle, how to optimize with given subset of IDs

I have a very slow query, not sure how to fix this

SELECT d.I_CUSTOMER_ID
FROM CUSTOMER d
WHERE EXISTS (
SELECT 1
FROM CUSTOMER_BACKUP cs
WHERE cs.I_CUSTOMER_ID = d.I_CUSTOMER_ID
AND cs.s_status != 'R')
OR customer_chrg.f_get_backup(d.I_CUSTOMER_ID) = 0;


I already have some IDs that could fit in a IN statement, for example IN (1,2,3,4,10,11) but I am not sure how to narrow the query using an IN statement (if applicable)

The query scans the customer table and retrieves all the ids that either exists on the customer backup table (with status != 'R') OR for all customer executes a slow f_get_backup procedure.

That takes a long time and I am looking for a better solution since I don't want to scan all the table but only a portion of it (IDs that I already have from a client call)

Continue reading...