How do I delete rows from 1 table that don't exist in another table?

[mikea64]

I have 2 tables in Oracle8.1.7. Table A has 2 million rows, and Table B has 500,000 rows. Table B is a subset of Table A. Table A has referencial constraints to 3 other tables and CASCADE CONSTRAINTS is not enabled. How do I delete rows in Table A that don't exist in Table B, without using 'WHERE ... NOT IN' because the NOT IN disables the indexes and causes a full table scan for each row in Table B? How do I then delete the orphaned records in the 3 RI tables?

We are trying to find the solution that runs in the shortest amount of time.

Thanks.

 

[sem]

Try to use NOT EXISTS(..)
But in any case you should perform a full scan on A

 

[EdWall]

I suggest that you use an Outer Join on from table A to Table B and limit the records retrieved so that you don't fill up the rollback segment. Then loop round the records in Table A and delete the associated reference records.

DECLARE
CURSOR del_cursor
SELECT A.pk, A.ROWID
FROM a, b
WHERE a.pk = b.pk (+)
AND b.pk IS NULL
AND ROWNUM <= 5000

BEGIN
recs_to_delete := TRUE;
WHILE recs_to_delete
LOOP
recs_to_delete := FALSE;

FOR del_rec IN del_cursor
LOOP
DELETE ref1 WHERE ref1.pk = del_rec.pk;
DELETE ref2 WHERE ref2.pk = del_rec.pk;
DELETE a WHERE a.ROWID = del_rec.rowid;
recs_to_delete := TRUE;
END LOOP;

COMMIT;

END LOOP;
END;

Good luck

Ed

 

[mikea64]

Thanks EdWall,

I'll pass this along to the poor guy who will be performing this and see how it goes.