CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10gquery-optimization
Jerry
Jerry

Reputation: 149

Oracle SQL Query Performance w/ NOT IN

I am attempting to get a list of all client numbers from one of our databases. I only want the clients that have no patients and no billing history.

The query I have works (returns correct results)...but takes ~16hrs to run. I suspect it could be done much more efficiently, but I need some pointers to get me started in the right direction.

SELECT A.account_number FROM ClientTable A
WHERE A.account_number NOT IN (SELECT B.account_number FROM PatientTable B) AND
        A.account_number NOT IN (SELECT C.account_number FROM BillingTable C);

ClientTable has ~10million rows

PatientTable has ~12million rows

BillingTable has ~25million rows

Is there a better way to do this?

Upvotes: 1

Views: 85

Answers (2)

JammyJoker
JammyJoker

Reputation: 46

NOT IN is often expensive. Sometimes a LEFT JOIN and a filter condition can work, but this is dependant on your situation so performance wise I would urge caution, and check your Explain Plan, especially with many rows and tables.

SELECT A.account_number 
FROM   ClientTable A
LEFT OUTER JOIN PatientTable B ON A.account_number = B.account_Number
LEFT OUTER JOIN BillingTable C ON A.account_number = C.account_Number
WHERE B.account_number IS NULL AND C.account_number IS NULL;

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 142710

Would sets do any good & improvement?

(select account_number from clientTable
 minus
 select account_number from patientTable
)
 union    --> optionally, UNION ALL
(select account_number from clientTable
 minus
 select account_number from billingTable
);

UNION ALL instead of UNION should be faster as it wouldn't have to remove duplicates.

Upvotes: 0

Related Questions