Purge records that don't have a relationship

From: Jeff Lambert <jeffl_at_hypershell.com>
Date: 28 Apr 2004 10:34:09 -0700
Message-ID: <f17e7766.0404280934.fa831f5@posting.google.com>

Hi, I'm using Oracle 9i on Windows 2k / XP.

I have two tables

OCCWORD

---

PKREF (NUM) OCCSRC

---

PKSOURCE (NUM)
FKREF (NUM) I want to delete all record from OCCWORD which contain a PKREF which isn't found in OCCSRC. Under MSSQL the following statement works:

DELETE FROM OCCWORD WHERE PKREF NOT IN (SELECT DISTINCT FKREF FROM OCCSRC); But unfortunately in Oracle, the server chokes on it. This has probably to do with the fact that the OCCWORD has around 8,000,000 records and the OCCSRC is around 600,000 records.

I am willing to move to a stored procedure. I understand Oracle builds undo statements for all of its deletes, or an execution plan or something, which possibly eats up all of the memory. In the past, when deleting all records from large tables, I have used a TRUNCATE_TABLE procedure that I have found on these newsgroups. But I don't want to completely flush the tables in my case.

Can anybody help? TIA.

Jeff Lambert Received on Wed Apr 28 2004 - 12:34:09 CDT