Re: Purge records that don't have a relationship

You don't mention what is happening in terms of "choking", but you might want to try something like the following:

DELETE FROM OCCWORD WHERE PKREF IN
(SELECT PKREF FROM OCCWORD MINUS SELECT FKREF FROM OCCSRC); --Peter

Jeff Lambert wrote:
> 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 - 15:18:56 CDT