I actually have a select that runs very fast, as long as I don't use
IN....

The SQL is like this

SELECT
SUM( VARER_DETAIL.ANTALSTK ) ANTALSTK,
SUM( VARER_DETAIL.BEH_KOSTPRIS ) BEH_KOSTPRIS,
SUM( VARER_DETAIL.BEH_SALGSPRIS ) SALGSPRIS,
SUM( VARER_DETAIL.BEH_SALGSPRISMOMS ) SALGSPRISMOMS,
VARER_DETAIL.AFDELING_ID,
VARER.LEVERID GRUP2,
LEVERANDOERER.V509Index GRUP2509,
Afdeling.Navn,
Afdeling.AfdInfo
FROM VARER_DETAIL
Inner Join Afdeling on (Afdeling.Afdelingsnummer =
Varer_Detail.Afdeling_ID)
Inner Join Varer on (Varer.Plu_Nr=Varer_Detail.VarePlu_ID)
Inner Join LEVERANDOERER on (Varer.LeverID=LEVERANDOERER.Navn)
Where (1=1)
And Varer_detail.Afdeling_ID='001'
GROUP BY
VARER_DETAIL.AFDELING_ID,
LEVERANDOERER.V509Index,
VARER.LEVERID,
Afdeling.Navn,
Afdeling.AfdInfo

This uses an adapted plan like this:

Adapted Plan
PLAN SORT (JOIN (VARER NATURAL,AFDELING INDEX
(INTEG_173),VARER_DETAIL INDEX (INTEG_194),LEVERANDOERER INDEX
(INTEG_190)))



The minute I change the VARER_DETAIL.AFDELING_ID to a IN ... like this

SELECT
SUM( VARER_DETAIL.ANTALSTK ) ANTALSTK,
SUM( VARER_DETAIL.BEH_KOSTPRIS ) BEH_KOSTPRIS,
SUM( VARER_DETAIL.BEH_SALGSPRIS ) SALGSPRIS,
SUM( VARER_DETAIL.BEH_SALGSPRISMOMS ) SALGSPRISMOMS,
VARER_DETAIL.AFDELING_ID,
VARER.LEVERID GRUP2,
LEVERANDOERER.V509Index GRUP2509,
Afdeling.Navn,
Afdeling.AfdInfo
FROM VARER_DETAIL
Inner Join Afdeling on (Afdeling.Afdelingsnummer =
Varer_Detail.Afdeling_ID)
Inner Join Varer on (Varer.Plu_Nr=Varer_Detail.VarePlu_ID)
Inner Join LEVERANDOERER on (Varer.LeverID=LEVERANDOERER.Navn)
Where (1=1)
And Varer_detail.Afdeling_ID IN ('001','002')
GROUP BY
VARER_DETAIL.AFDELING_ID,
LEVERANDOERER.V509Index,
VARER.LEVERID,
Afdeling.Navn,
Afdeling.AfdInfo

The adapted plan changes to:

Adapted Plan
PLAN SORT (JOIN (LEVERANDOERER NATURAL,VARER INDEX
(INTEG_258),VARER_DETAIL INDEX
(INTEG_260,INTEG_261,INTEG_261),AFDELING INDEX (INTEG_173)))


This last one runs in average 7 times longer, doing a

SELECT
SUM( VARER_DETAIL.ANTALSTK ) ANTALSTK,
SUM( VARER_DETAIL.BEH_KOSTPRIS ) BEH_KOSTPRIS,
SUM( VARER_DETAIL.BEH_SALGSPRIS ) SALGSPRIS,
SUM( VARER_DETAIL.BEH_SALGSPRISMOMS ) SALGSPRISMOMS,
VARER_DETAIL.AFDELING_ID,
VARER.LEVERID GRUP2,
LEVERANDOERER.V509Index GRUP2509,
Afdeling.Navn,
Afdeling.AfdInfo
FROM VARER_DETAIL
Inner Join Afdeling on (Afdeling.Afdelingsnummer =
Varer_Detail.Afdeling_ID)
Inner Join Varer on (Varer.Plu_Nr=Varer_Detail.VarePlu_ID)
Inner Join LEVERANDOERER on (Varer.LeverID=LEVERANDOERER.Navn)
Where (1=1)
And
(
(Varer_detail.Afdeling_ID='001')
)
GROUP BY
VARER_DETAIL.AFDELING_ID,
LEVERANDOERER.V509Index,
VARER.LEVERID,
Afdeling.Navn,
Afdeling.AfdInfo



Union


SELECT
SUM( VARER_DETAIL.ANTALSTK ) ANTALSTK,
SUM( VARER_DETAIL.BEH_KOSTPRIS ) BEH_KOSTPRIS,
SUM( VARER_DETAIL.BEH_SALGSPRIS ) SALGSPRIS,
SUM( VARER_DETAIL.BEH_SALGSPRISMOMS ) SALGSPRISMOMS,
VARER_DETAIL.AFDELING_ID,
VARER.LEVERID GRUP2,
LEVERANDOERER.V509Index GRUP2509,
Afdeling.Navn,
Afdeling.AfdInfo
FROM VARER_DETAIL
Inner Join Afdeling on (Afdeling.Afdelingsnummer =
Varer_Detail.Afdeling_ID)
Inner Join Varer on (Varer.Plu_Nr=Varer_Detail.VarePlu_ID)
Inner Join LEVERANDOERER on (Varer.LeverID=LEVERANDOERER.Navn)
Where (1=1)
And
(
(Varer_detail.Afdeling_ID='002')
)
GROUP BY
VARER_DETAIL.AFDELING_ID,
LEVERANDOERER.V509Index,
VARER.LEVERID,
Afdeling.Navn,
Afdeling.AfdInfo


Which has an adapted plan like:


Adapted Plan
PLAN SORT (JOIN (VARER NATURAL,AFDELING INDEX
(INTEG_173),VARER_DETAIL INDEX (INTEG_194),LEVERANDOERER INDEX
(INTEG_190))) PLAN SORT (JOIN (VARER NATURAL,AFDELING INDEX
(INTEG_173),VARER_DETAIL INDEX (INTEG_194),LEVERANDOERER INDEX
(INTEG_190)))

So I have skipped using the IN in this SQL.
If you like, I can provide you with a backup of the actual DB !


Regards
Michael