You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
SELECT
ITEM_NOTA_COMPRA.INC_ID
FROM ITEM_NOTA_COMPRA
LEFT JOIN NOTA_COMPRA
ON (ITEM_NOTA_COMPRA.NTC_ID = NOTA_COMPRA.NTC_ID)
WHERE (ITEM_NOTA_COMPRA.INC_ID_COMPLEMENTADO = 651321)
AND (NOTA_COMPRA.NTC_SITUACAO = 'Finalizada')
Field INC_ID_COMPLEMENTADO is a foreign key and all record values are null (index stats = 1).
Firebird 2.5.9 uses the FK index from INC_ID_COMPLEMENTADO field:
PLAN JOIN (ITEM_NOTA_COMPRA INDEX (FK_INC_INC_COMPLEMENTADO), NOTA_COMPRA INDEX (PK_NOTA_COMPRA))
Records fetched = 0
Prepare time = 0ms
Execute time = 0ms
Reads from disk to cache = 0
Fetches from cache = 24
Firebird 5.0.1 converts to inner join and do a full scan on right table:
PLAN JOIN (NOTA_COMPRA NATURAL, ITEM_NOTA_COMPRA INDEX (FK_ITEM_NTC_NOTA_COMPRA))
Records fetched = 0
Prepare time = 0ms
Execute time = 1s 0ms
Reads from disk to cache = 13.592
Fetches from cache = 871.441
With "OuterJoinConversion = false", they use the same plan.
Workaround:
Create an "is not null" partial index on field INC_ID_COMPLEMENTADO (stats = 0).
But, then you have two indexes on same field, because it's a foreign key.
The text was updated successfully, but these errors were encountered:
Given the query below:
SELECT
ITEM_NOTA_COMPRA.INC_ID
FROM ITEM_NOTA_COMPRA
LEFT JOIN NOTA_COMPRA
ON (ITEM_NOTA_COMPRA.NTC_ID = NOTA_COMPRA.NTC_ID)
WHERE (ITEM_NOTA_COMPRA.INC_ID_COMPLEMENTADO = 651321)
AND (NOTA_COMPRA.NTC_SITUACAO = 'Finalizada')
Field INC_ID_COMPLEMENTADO is a foreign key and all record values are null (index stats = 1).
Firebird 2.5.9 uses the FK index from INC_ID_COMPLEMENTADO field:
PLAN JOIN (ITEM_NOTA_COMPRA INDEX (FK_INC_INC_COMPLEMENTADO), NOTA_COMPRA INDEX (PK_NOTA_COMPRA))
Records fetched = 0
Prepare time = 0ms
Execute time = 0ms
Reads from disk to cache = 0
Fetches from cache = 24
Firebird 5.0.1 converts to inner join and do a full scan on right table:
PLAN JOIN (NOTA_COMPRA NATURAL, ITEM_NOTA_COMPRA INDEX (FK_ITEM_NTC_NOTA_COMPRA))
Records fetched = 0
Prepare time = 0ms
Execute time = 1s 0ms
Reads from disk to cache = 13.592
Fetches from cache = 871.441
With "OuterJoinConversion = false", they use the same plan.
Workaround:
Create an "is not null" partial index on field INC_ID_COMPLEMENTADO (stats = 0).
But, then you have two indexes on same field, because it's a foreign key.
The text was updated successfully, but these errors were encountered: