Deleting data from multiple linked tables

There are three tables linked one-to-one by ID. I need to delete all records from three tables that satisfy the condition: A.ID = B.ID = C.ID.

Now I do it in the following way:

DECLARE
    CURSOR CUR IS 
        SELECT C.ID FROM A 
        INNER JOIN B ON A."ID" = B."ID" 
        INNER JOIN C ON B."ID" = C."ID"
        WHERE A.STATUS = 'ERROR';
    IDX NUMBER;
BEGIN
    FOR REC IN CUR LOOP 
        IDX := REC.ID;
        DELETE FROM C WHERE C."ID" = IDX;
        DELETE FROM B WHERE B."ID" = IDX;
        DELETE FROM A WHERE BP."ID" = IDX;
    END LOOP;
    COMMIT;
END;

There is a lot of data and so it works for a very long time.

Is there any way to make it faster?

Author: 0xdb, 2012-10-30
Source

4 answers

Create a table to copy the matching IDs to.

Delete data from all three tables that have the same IDs as the ones in the new table.

Delete the new table.

 3
Author: Modus, 2012-10-30 09:08:03

It seems that there are Foreign Keys. When creating such a key, you can specify

ON DELETE CASCADE
ON UPDATE CASCADE

That is, if it is deleted from A, then from B, C will be deleted automatically. It also depends on the semantics of your tables.

 2
Author: ЫЫЫ, 2012-10-30 06:13:12

Repeating requests in a loop = = performance killer. Try this way:

create table a as 
    select rownum id, substrb (column_value, 1, 5) status 
    from xmlTable ('"OK", "ERROR", "ERROR"');
create table b as select id from a;
create table c as select id from b;

declare
    targets sys.odciNumberList; 
begin 
    select a.id bulk collect into targets 
    from a 
    join b on b.id = a.id 
    join c on c.id = b.id
    where a.status = 'ERROR' for update;

    delete from c where exists (
        select 1 from table (targets) where column_value=c.id); 
    delete from b where exists (
        select 1 from table (targets) where column_value=b.id); 
    delete from a where exists (
        select 1 from table (targets) where column_value=a.id);
    dbms_output.put_line (targets.count||' id''s deleted.');
end;
/

2 id's deleted.
 2
Author: 0xdb, 2020-04-15 17:15:47

You have already been told about the foreign key, but I will note that the cursor is not needed here at all. If I read it correctly, then such queries are enough:

DELETE FROM C WHERE C."ID" IN(SELECT "ID" FROM A WHERE STATUS = 'ERROR');

, etc.

 1
Author: msi, 2012-10-30 06:28:36