Re: Purge records that don't have a relationship

> 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

For such biggie tables, I found that NOT EXISTS works much better than NOT IN. Hope also you use CBO. Also, your DISTINCT is completely useless, and is going to possibly trigger 8,000,000 disk-based sorts! In addition to using NOT EXISTS, use a large enough hash area, and use the hint HASH_SJ. You will see, it should run pretty quickly.

HTH Daniel Received on Wed Apr 28 2004 - 18:42:28 CDT