使用in (select ...)进行查询优化子句
使用in (select ...)进行查询优化子句
提问于 2019-08-08 01:33:52
我在Windows上使用Firebird WI-V3.0.4.33054。
我在优化这个查询时遇到了问题，这个查询使用了一个带有select的in子句：
update CADPC p set p.STA = 'L'
where p.COD in (select distinct CODPC from CADPCI_Rec where IDNfr = 27)
and not exists (select * from CADPCI where CODPC = p.COD)这个查询的计划是(最明显的问题是P NATURAL部分)：
PLAN SORT (CADPCI_REC INDEX (PK_CADPCI_REC))
PLAN (CADPCI INDEX (FK_CADPCI_CODPC))
PLAN (P NATURAL)
Select Expression
-> Filter
-> Unique Sort (record length: 36, key length: 8)
-> Filter
-> Table "CADPCI_REC" Access By ID
-> Bitmap
-> Index "PK_CADPCI_REC" Range Scan (partial match: 1/3)
Select Expression
-> Filter
-> Table "CADPCI" Access By ID
-> Bitmap
-> Index "FK_CADPCI_CODPC" Range Scan (full match)
Select Expression
-> Filter
-> Table "CADPC" as "P" Full Scan另一方面，如果我手动运行select distinct，复制结果并粘贴到查询中，如下所示：
update CADPC p set p.STA = 'L'
where p.COD in (5699, 5877, 5985)
and not exists (select * from CADPCI where CODPC = p.COD)现在优化器为P表选择了一个合理的计划，查询运行得非常快：
PLAN (CADPCI INDEX (FK_CADPCI_CODPC))
PLAN (P INDEX (PK_CADPC, PK_CADPC, PK_CADPC))
Select Expression
-> Filter
-> Table "CADPCI" Access By ID
-> Bitmap
-> Index "FK_CADPCI_CODPC" Range Scan (full match)
Select Expression
-> Filter
-> Table "CADPC" as "P" Access By ID
-> Bitmap Or
-> Bitmap Or
-> Bitmap
-> Index "PK_CADPC" Unique Scan
-> Bitmap
-> Index "PK_CADPC" Unique Scan
-> Bitmap
-> Index "PK_CADPC" Unique Scan我还尝试了在这两个条件中使用exists，但结果是相同的:对每一行重新计算该子查询。
update CADPC p set p.STA = 'L'
where exists (select * from CADPCI_Rec where IDNfr = 27 and CODPC = p.COD)
and not exists (select * from CADPCI where CODPC = p.COD)计划：
PLAN (CADPCI_REC INDEX (PK_CADPCI_REC))
PLAN (CADPCI INDEX (FK_CADPCI_CODPC))
PLAN (P NATURAL)
Select Expression
-> Filter
-> Table "CADPCI_REC" Access By ID
-> Bitmap
-> Index "PK_CADPCI_REC" Range Scan (partial match: 1/3)
Select Expression
-> Filter
-> Table "CADPCI" Access By ID
-> Bitmap
-> Index "FK_CADPCI_CODPC" Range Scan (full match)
Select Expression
-> Filter
-> Table "CADPC" as "P" Full Scan因此，问题是:当in子句包含select (通常只有几条记录)时，我能以某种方式让引擎选择索引计划吗？
回答 1
Stack Overflow用户
发布于 2019-08-08 03:21:28
尝试将exists用于这两种情况：
update CADPC p
set p.STA = 'L'
where exists (select 1 from CADPCI_Rec where r.IDNfr = 27 and p.COD = r.CODPC) and
not exists (select 1 from CADPCI c2 where c2.CODPC = p.COD);特别是，您需要一个关于CADPCI_Rec(CODPC, IDNfr)的索引。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57399622
复制相关文章
相似问题
腾讯云开发者
