我的 firebird 查询计划未使用正确的索引

发布于 2024-11-29 22:42:26 字数 1091 浏览 6 评论 0原文

我的 firebird 查询中的索引有问题。

以下是我的查询。

SELECT a.objid,
       b.running_qty,
       b.running_qty2,
       b.running_totalcost,
       b.running_lastcost
FROM mm_itrghd a,
     mm_itrgdt b
WHERE (a.objid = b.header_id)
AND   (b.item_id = 1200)
AND   (b.wh_id = 1)
AND   ((a.postdate < '2010-09-05 00:00:00')  OR ((a.postdate = '2010-09-05 00:00:00') AND (a.objid < 50000)))
ORDER BY a.postdate desc,
         a.objid desc,
         b.calctyp desc,
         b.objid desc

如您所见，按部分顺序，我们使用 desc。我有一个降序索引，但我的查询计划不使用它。它只使用索引表A（a.objid）和表B（b.item_id，b.wh_id）有什么我错过的吗？您认为我应该创建什么索引？

表 A 的索引 (mm_itrghd)

(TR_CODE, DOC_ID) 升序 (OBJID) 升序 (TR_CODE) 升序（后日期）升序（POSTDATE，OBJID）升序 (POSTDATE, OBJID)

表 B 的降序索引 (mm_itrgdt)

(HEADER_ID) 升序 (ITEM_ID) 升序 (WH_ID) 升序 (LOT_NO) 升序（SERIAL_NO、ITEM_ID）升序 (HEADER_ID、ITEM_ID、WH_ID、SERIAL_NO、LOT_NO) 升序 (HEADER_ID、ITEM_ID、WH_ID) 升序（CALCTYP、OBJID）升序 (ITEM_ID, WH_ID) 升序 (CALCTYP、OBJID、ITEM_ID、WH_ID) 升序（CALCTYP、OBJID）降序（OBJID、ITEM_ID、WH_ID）降序 (OBJID) 降序

提前致谢

问候，雷纳尔迪

原文

I have a problem with indexes in my firebird query.

Below is my query.

SELECT a.objid,
       b.running_qty,
       b.running_qty2,
       b.running_totalcost,
       b.running_lastcost
FROM mm_itrghd a,
     mm_itrgdt b
WHERE (a.objid = b.header_id)
AND   (b.item_id = 1200)
AND   (b.wh_id = 1)
AND   ((a.postdate < '2010-09-05 00:00:00')  OR ((a.postdate = '2010-09-05 00:00:00') AND (a.objid < 50000)))
ORDER BY a.postdate desc,
         a.objid desc,
         b.calctyp desc,
         b.objid desc

As you see, in order by section, we use desc. I have an descending index, but my query plan does not use it.
It only use index Table A (a.objid) and Table B (b.item_id, b.wh_id)
Is there something i missed? What index do you think should i create?

Index for Table A (mm_itrghd)

(TR_CODE, DOC_ID) Ascending
(OBJID) Ascending
(TR_CODE) Ascending
(POSTDATE) Ascending
(POSTDATE, OBJID) Ascending
(POSTDATE, OBJID) Descending

Index for Table B (mm_itrgdt)

(HEADER_ID) Ascending
(ITEM_ID) Ascending
(WH_ID) Ascending
(LOT_NO) Ascending
(SERIAL_NO, ITEM_ID) Ascending
(HEADER_ID, ITEM_ID, WH_ID, SERIAL_NO, LOT_NO) Ascending
(HEADER_ID, ITEM_ID, WH_ID) Ascending
(CALCTYP, OBJID) Ascending
(ITEM_ID, WH_ID) Ascending
(CALCTYP, OBJID, ITEM_ID, WH_ID) Ascending
(CALCTYP, OBJID) Descending
(OBJID, ITEM_ID, WH_ID) Descending
(OBJID) Descending

Thanks in advance

Regards,
Reynaldi

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

孤千羽 2024-12-06 22:42:26

首先更新索引统计信息，因为 Firebird 在选择使用什么索引和不使用什么索引时会依赖它。要么对数据库进行备份恢复周期，要么执行以下代码：

EXECUTE BLOCK
AS
  DECLARE VARIABLE IDX VARCHAR(31);
BEGIN
  FOR
    SELECT rdb$index_name FROM rdb$indices
    WHERE NOT rdb$index_name LIKE 'RDB$%'
    INTO :idx
  DO BEGIN
    EXECUTE STATEMENT 'update statistics ' || :idx
    WITH AUTONOMOUS TRANSACTION; 
  END
END

之后检查查询计划。如果不使用索引，那是因为 Firebird 认为使用它弊大于利。您可以手动指定查询计划或尝试重写它。

在您的情况下，您可以使用 UNION 运算符摆脱 OR 条件：

select 
  a.postdate, 
  a.objid, 
  b.calctyp, 
  b.objid,
  b.running_qty, 
  b.running_qty2, 
  b.running_totalcost, 
  b.running_lastcost 
from 
  mm_itrghd a join mm_itrgdt b 
    on a.objid=b.header_id 
where 
  (b.item_id=1200)
  and (b.wh_id=1) 
  and (a.postdate<'2010-09-05 00:00:00') 

union all

select 
  a.postdate, 
  a.objid, 
  b.calctyp, 
  b.objid,
  b.running_qty, 
  b.running_qty2, 
  b.running_totalcost, 
  b.running_lastcost 
from 
  mm_itrghd a join mm_itrgdt b 
    on a.objid=b.header_id 
where 
  (b.item_id=1200)
  and (b.wh_id=1) 
  and (a.postdate='2010-09-05 00:00:00') 
  and (a.objid<50000)

order by 
  1 desc, 2 desc, 3 desc, 4 desc

First of all update indices statistics because Firebird relay on it when choosing what index to use and what not. Either do backup-restore cycle for database or execute following code:

EXECUTE BLOCK
AS
  DECLARE VARIABLE IDX VARCHAR(31);
BEGIN
  FOR
    SELECT rdb$index_name FROM rdb$indices
    WHERE NOT rdb$index_name LIKE 'RDB$%'
    INTO :idx
  DO BEGIN
    EXECUTE STATEMENT 'update statistics ' || :idx
    WITH AUTONOMOUS TRANSACTION; 
  END
END

Check the query plan after that. If index is not used it is because Firebird thinks that its usage will do more harm then help. You can either specify plan for query manually or try to rewrite it.

In your case you can get rid of OR condition using UNION operator:

select 
  a.postdate, 
  a.objid, 
  b.calctyp, 
  b.objid,
  b.running_qty, 
  b.running_qty2, 
  b.running_totalcost, 
  b.running_lastcost 
from 
  mm_itrghd a join mm_itrgdt b 
    on a.objid=b.header_id 
where 
  (b.item_id=1200)
  and (b.wh_id=1) 
  and (a.postdate<'2010-09-05 00:00:00') 

union all

select 
  a.postdate, 
  a.objid, 
  b.calctyp, 
  b.objid,
  b.running_qty, 
  b.running_qty2, 
  b.running_totalcost, 
  b.running_lastcost 
from 
  mm_itrghd a join mm_itrgdt b 
    on a.objid=b.header_id 
where 
  (b.item_id=1200)
  and (b.wh_id=1) 
  and (a.postdate='2010-09-05 00:00:00') 
  and (a.objid<50000)

order by 
  1 desc, 2 desc, 3 desc, 4 desc

回复收藏 0 原文