Faster DELETE w/ JOIN query

Question

I have this query in Oracle 10g:

 DELETE FROM "BMAN_TP1"."CELLS_ITEM" TABLE1
 WHERE EXISTS (
     SELECT "CELLS_ITEM".*
     FROM "BMAN_TP1"."CELLS_ITEM"
     INNER JOIN "BMAN_TP1"."CELLS" ON ("CELLS_ITEM"."SET_ID"="CELLS"."SET_ID") AND ("CELLS_ITEM"."META_CELL_ID"="CELLS"."META_CELL_ID")
     INNER JOIN "BMAN_TP1"."META_CELLS" ON ("CELLS"."META_CELL_ID"="META_CELLS"."META_CELL_ID")
     WHERE ("META_CELLS"."UDA_ID" = variable)
     AND (TABLE1."SET_ID" = "CELLS_ITEM"."SET_ID")
     AND (TABLE1."META_CELL_ID" = "CELLS_ITEM"."META_CELL_ID")
)

which currently takes about 10 sec for 50K records to delete (and about 100K records in the table)

I know that it repeats 100K times the select query, that slows it down a lot.
Also TABLE1 has a two-fields PK, which makes the things more complicated.

Any ideas to make it faster?

EDIT:

Tried this one but it takes almost the same:

DELETE FROM "BMAN_TP1"."CELLS_ITEM" TABLE1
WHERE EXISTS (
    SELECT "META_CELL_ID"
    FROM "BMAN_TP1"."META_CELLS"
    WHERE ("META_CELLS"."UDA_ID"=55823)
    AND (TABLE1."META_CELL_ID" = "META_CELLS"."META_CELL_ID")
)

Solution 1

Without knowing your schema, it is hard to tell, but using the table you want to delete from in the subquery seems useless. I would write instead:

DELETE FROM BMAN_TP1.CELLS_ITEM TABLE1
WHERE EXISTS (
    SELECT CELLS.META_CELL_ID
    FROM BMAN_TP1.CELLS 
    INNER JOIN BMAN_TP1.META_CELLS ON (CELLS.META_CELL_ID=META_CELLS.META_CELL_ID)
    WHERE (META_CELLS.UDA_ID = variable)
        AND (TABLE1.SET_ID = CELLS_ITEM.SET_ID)
        AND (TABLE1.META_CELL_ID = CELLS_ITEM.META_CELL_ID)
)

EDIT: the above is dated now, since you modified your DELETE statement. Please ignore it.

But another idea: if there are triggers defined on CELLS_ITEM, you can try disabling them. They can chew on bigger deletes for quite long, I know it first-hand.

Solution 2

First possible answer: Just add an index to your CELLS_ITEM table on SET_ID, META_CELL_ID

Second possible answer: try the standard SQL syntax:

DELETE "BMAN_TP1"."CELLS_ITEM"
  FROM BMAN_TP1"."CELLS_ITEM"  
 INNER JOIN "BMAN_TP1"."CELLS" ON ("CELLS_ITEM"."SET_ID"="CELLS"."SET_ID") AND ("CELLS_ITEM"."META_CELL_ID"="CELLS"."META_CELL_ID")  
 INNER JOIN "BMAN_TP1"."META_CELLS" ON ("CELLS"."META_CELL_ID"="META_CELLS"."META_CELL_ID")  
 WHERE ("META_CELLS"."UDA_ID" = variable)  

-- EDIT

OK, if it's true that Oracle does not accept the standard SQL way (sounds odd) then you could try using an IN:

DELETE "BMAN_TP1"."CELLS_ITEM"
 WHERE (SET_ID, META_CELL_ID) IN (SELECT SET_ID, META_CELL_ID
                                    FROM BMAN_TP1"."CELLS_ITEM"  
                                         INNER JOIN "BMAN_TP1"."CELLS" ON ("CELLS_ITEM"."SET_ID"="CELLS"."SET_ID") AND ("CELLS_ITEM"."META_CELL_ID"="CELLS"."META_CELL_ID")  
                                         INNER JOIN "BMAN_TP1"."META_CELLS" ON ("CELLS"."META_CELL_ID"="META_CELLS"."META_CELL_ID")  
                                   WHERE ("META_CELLS"."UDA_ID" = variable)  )

but I think the EXISTS query should be faster than this one... which would leave the adding an index answer as your best option. But just to be sure, try this new approach first.

Solution 3

Try this:

DELETE FROM (SELECT TABLE1.* FROM "BMAN_TP1"."CELLS_ITEM" TABLE1
                INNER JOIN (
                    SELECT "META_SET_ID", "META_CELL_ID"
                    FROM "BMAN_TP1"."META_CELLS"
                    WHERE "UDA_ID"=55823
                ) j ON TABLE1."SET_ID" = j."META_SET_ID" AND TABLE1."META_CELL_ID" = j."META_CELL_ID"
            )

Solution 4

I can't test so not sure right now as I don't have access to Oracle DB right now, but rowid could be faster in some cases:

   DELETE "BMAN_TP1"."CELLS_ITEM"
 WHERE rowid IN (SELECT rowid
                                    FROM BMAN_TP1"."CELLS_ITEM"  
                                         INNER JOIN "BMAN_TP1"."CELLS" ON ("CELLS_ITEM"."SET_ID"="CELLS"."SET_ID") AND ("CELLS_ITEM"."META_CELL_ID"="CELLS"."META_CELL_ID")  
                                         INNER JOIN "BMAN_TP1"."META_CELLS" ON ("CELLS"."META_CELL_ID"="META_CELLS"."META_CELL_ID")  
                                   WHERE ("META_CELLS"."UDA_ID" = variable)  )

View more solutions