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.

Various ways:

http://groups.google.com/groups?selm=6ccuOHl1o2azn3QBxxLasW4Ibdiz%404ax.com&oe=UTF-8&output=gplain

http://www.psoug.org/reference/bulk_collect.html

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&th=85e05583ba2ba878&rnum=1

jg

--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20040428/news_lz1c28fusion.html