Subject | Disable index in FB 2 Group By statements |
---|---|
Author | Rick Debay |
Post date | 2006-11-21T21:51:46Z |
This will only return one row, so the ORDER part of the FB2 plan is
superfluous, and makes the query slower than the FB 1.5 plan.
SELECT
pbi.QTY, COALESCE(SUM(cbi.QTY_SHIP),0)
FROM
RPL_POBILL_ITM pbi
LEFT JOIN RPL_CLM_POBILL_ITM cbi
ON cbi.POBILL_ITM = pbi.ID
WHERE
pbi.ID = ?
GROUP BY
pbi.QTY
FB 2
PLAN JOIN (PBI ORDER I_RPL_POBILL_ITM_QTY INDEX (PK_RPL_POBILL_ITM), CBI
INDEX (FK_RPLCLMPOBILLITM_POBILLITM))
FB 1.5
PLAN SORT (JOIN (PBI INDEX (PK_RPL_POBILL_ITM),CBI INDEX
(FK_RPLCLMPOBILLITM_POBILLITM)))
How do I keep FB2 from trying to sort on an index when I'm guaranteed to
have only one row?
superfluous, and makes the query slower than the FB 1.5 plan.
SELECT
pbi.QTY, COALESCE(SUM(cbi.QTY_SHIP),0)
FROM
RPL_POBILL_ITM pbi
LEFT JOIN RPL_CLM_POBILL_ITM cbi
ON cbi.POBILL_ITM = pbi.ID
WHERE
pbi.ID = ?
GROUP BY
pbi.QTY
FB 2
PLAN JOIN (PBI ORDER I_RPL_POBILL_ITM_QTY INDEX (PK_RPL_POBILL_ITM), CBI
INDEX (FK_RPLCLMPOBILLITM_POBILLITM))
FB 1.5
PLAN SORT (JOIN (PBI INDEX (PK_RPL_POBILL_ITM),CBI INDEX
(FK_RPLCLMPOBILLITM_POBILLITM)))
How do I keep FB2 from trying to sort on an index when I'm guaranteed to
have only one row?