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

Jeff, is there an index on occsrc.fkref? If not run an explain plan. Then add the index and run a new plan. If the plan changed to take advantage of the index then that may be enough.

If the plan did not change or the index exists then write a short pl/sql routine that opens a driving cursor on OCCWORD and issues a delete from OCCSRC where not exists (select 'x' from OCCSRC where occsrc.fkref = occword.fkref).

I suggest you check the explain plan to make sure the index is being used with the corrdinated sub-query if this approach becomes necessary.

Make the commit frequency large so as to use a large portion of available rollback. This will decrease the chance of getting a 1555 error made likely by reading and updating the same table while committing where a large number of rows are changed. But if you do get a snapshot too old error you can just restart the script and it will have less work to do each time it runs with the pl/sql routine.

If the plan can use an index and the total number of rows without a match is small then you may just be able to run the statement via sqlplus with only one commit.

HTH -- Mark D Powell -- Received on Wed Apr 28 2004 - 18:46:49 CDT