|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
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
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.