Faster DELETE w/ JOIN query

join, oracle10g, sql, sql-delete

Solution

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.

Problem

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") ) ```

Original source

Related problems