How to optimize this DB operation?

Use EXISTS or JOIN instead of IN to improve perfromance

Using EXISTS:

DELETE FROM Atable A 
WHERE EXISTS (SELECT 1 FROM Btable B WHERE A.Btable_id = B.id AND B.param > 2) AND 
      EXISTS (SELECT 1 FROM Ctable C WHERE A.Ctable_id = C.id AND (C.someblob LIKE '%_ID1_%' OR C.someblob LIKE '%_ID2_%'))

Using JOIN:

DELETE A 
FROM Atable A 
INNER JOIN Btable B ON A.Btable_id = B.id AND B.param > 2
INNER JOIN Ctable C WHERE A.Ctable_id = C.id AND (C.someblob LIKE '%_ID1_%' OR C.someblob LIKE '%_ID2_%')

first you should try with exist instead of in. it's faster in many many case.

Then you could try to do inner join instead of in and exists.

Example :

delete a 
from a 
inner join b on b.id = a.tablebid

And finally if it could be possible (i don't know if you have id3, ids) to change the or by something else. Sometimes strange and complicated change helps the optimizer. case when, subquery...

I don't see where a simple index would help much. I'd do:

delete from atable where id in (
    select
        id
    from
        atable a
        join btable b on a.btable_id = b.id
        join ctable c on a.ctable_id = c.id
    where
        b.param > 2
        and (
            c.someblob LIKE '%_ID1_%' 
            OR c.someblob LIKE '%_ID2_%'
        )
)

Correction: I'm assuming you've got indexes on btable and ctable's id's (probably, if they're primary keys...) and on b.param (if it's numeric).

Beside optimizing the query you could also take a look at a good use of indexes, since they might prevent a full table scan.

For BTable for example create an index on id and param.

To explain why this helps: If the database has to look up the id and param values in the table in a unsorted manner, the database has to read ALL rows. If the database reads the index, SORTED, it can look up the id and param with reduced costs.