CODEWITHSUNDEEP
databaseoracle-database
Dan Vinton
Dan Vinton

Reputation: 26769

Database: deleting child records when the foreign key is on the parent

I have a pair of tables in an Oracle database with a one-to-one parent-child relationship. Unfortunately the foreign key is defined in the parent, not the child:

-----------------        -----------------        
| messages      |        | payloads      |
-----------------        -----------------
| id            |        | id            |
| payload_id    |------->| content       |
| creation_date |        -----------------
-----------------

The relationship from messages.payload_id to payloads.id is enforced by a non-deferrable foreign key.

We have a query that deletes all messages and payloads where message creation date is after a certain time. Unfortunately, due to the backwards foreign key, the current query looks like this:

DELETE FROM messages WHERE creation_date < deletion_date;
DELETE FROM payloads WHERE id NOT IN (SELECT payload_id FROM messages);

The second nasty delete statement is the problem, as it takes more than an hour when we have ~50 million records in each table.

Is there a better way to delete all messages and payloads?

Note that unfortunately the schema is beyond our control...

Upvotes: 0

Views: 1929

Answers (2)

David Aldridge
David Aldridge

Reputation: 52346

You could log the id's that you're going to delete into a global temporary table and then issue the deletes, optimising the delete from "messages" by storing the rowid as well

insert into my_temp_table (messages_rowid, payload_id)
select rowid, payload_id
from   messages
where  creation_date < deletion_date;

delete from messages
where  rowid in (select messages_rowid from my_temp_table);

delete from payload
where  id in (select payload_id from my_temnp_table);

commit;

Upvotes: 2

Thilo
Thilo

Reputation: 262504

How about 

DELETE FROM payloads WHERE id IN 
  ( SELECT payload_id FROM messages WHERE creation_date < deletion_date)

This needs to run before deleting from messages, of course.

Upvotes: 1

Related Questions