Re: Purge records that don't have a relationship

jeffl_at_hypershell.com (Jeff Lambert) wrote in message news:<f17e7766.0404280934.fa831f5_at_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

The NOT IN is slow, and the DELETE is slow, and the DISTINCT might be
slow. What you probably want to do is create a new table as select
what you want, drop old one and rename new one.