如何在 Firebird 中优化 DELETE .. NOT IN .. SUBQUERY [英] How to optimize DELETE .. NOT IN .. SUBQUERY in Firebird

问题描述

我有这种删除查询:

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

有什么办法可以优化吗?

Are there any way to optimize this?

推荐答案

可以使用EXECUTE BLOCK顺序扫描明细表，删除没有匹配到主记录的记录.

You can use EXECUTE BLOCK for sequential scanning of detail table and deleting records where no master record is matched.

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

这篇关于如何在 Firebird 中优化 DELETE .. NOT IN .. SUBQUERY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！