我在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 (通常只有几条记录)时,我能以某种方式让引擎选择索引计划吗?
https://stackoverflow.com/questions/57399622
复制相似问题