如何优化DELETE .. NOT IN .. SUBQUERY在火鸟(How to optimiz

我有这样的删除查询：

DELETE 
FROM SLAVE_TABLE
WHERE ITEM_ID NOT IN (SELECT ITEM_ID FROM MASTER_TABLE)

有没有什么办法来优化呢？

Answer 1:

您可以使用EXECUTE BLOCK详细表的顺序扫描和删除记录在没有主记录是匹配的。

EXECUTE BLOCK
AS
  DECLARE VARIABLE C CURSOR FOR
    (SELECT d.id
     FROM detail d LEFT JOIN master m
       ON d.master_id = m.id
     WHERE m.id IS NULL);
  DECLARE VARIABLE I INTEGER;
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :I;
    IF(ROW_COUNT = 0)THEN
      LEAVE;
    DELETE FROM detail WHERE id = :I;
  END
  CLOSE C;
END

Answer 2:

(NOT) IN通常可以通过使用被优化(NOT) EXISTS代替。

DELETE 
FROM SLAVE_TABLE
WHERE NOT EXISTS (SELECT 1 FROM MASTER_TABLE M WHERE M.ITEM_ID = ITEM_ID)

我不知道你正在尝试做的，但对我这个查询表明您应该使用外键来执行这些类型的限制，无法运行查询，清理残局之后。

文章来源: How to optimize DELETE .. NOT IN .. SUBQUERY in Firebird