Purge records that don't have a relationship

Giganews Newsgroups
Subject: Purge records that don't have a relationship
Posted by:  Jeff Lambert (jef…@hypershell.com)
Date: 28 Apr 2004

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

Replies