提高性能SQL

Question

在同一个内部查询多次运行的情况下，有没有办法定制下面的SQL来提高性能？

DELETE FROM interactionrel 
WHERE  ( from_interact_id IN(SELECT interaction_id 
                             FROM   interaction 
                             WHERE  instance_pk = ?) ) 
        OR ( to_interact_id IN(SELECT interaction_id 
                               FROM   interaction 
                               WHERE  instance_pk = ?) ) 

Answer 1

您可以尝试这样做--尽管您需要在优化器上运行它：

DELETE FROM interactionrel a
WHERE EXISTS (SELECT * 
              FROM interaction b
              WHERE b.instance_pl = ?
                    AND (b.interaction_id = a.from_interact_id
                         OR b.interaction_id = a.to_interact_id))

Answer 2

DELETE il 
FROM   interactionrel il, 
       interaction 
WHERE  ( interaction.interaction_id = il.from_interact_id 
          OR interaction.interaction_id = il.to_interact_id ) 
       AND interaction.instance_pk = ?