Hi Zsolt!
You cannot make something like query 2 get the exact same plan as query
1, but since MEGRENDELESID is unique for MEGRENDELESEK you can use a
join that will get a better plan:

SELECT MT.TERMEKKOD, MT.TERMEKNEV, SUM(MT.MENNYISEG) AS MENNYISEG,
SUM(MT.TELJESITETTMENNYISEG) AS TELJESITETTMENNYISEG, MT.EGYSEGAR,
SUM(MT.NETTOERTEK) AS NETTOERTEK, SUM(MT.AFAERTEK) AS AFAERTEK,
SUM(MT.BRUTTOERTEK) AS BRUTTOERTEK
FROM MEGRENDELESTETELEK MT
JOIN MEGRENDELESEK MS ON MS.MEGRENDELESID = MT.MEGRENDELESID
WHERE (MT.TIPUS=:T1 OR MT.TIPUS=:T2)
AND MS.MEGRENDELESID < 5000
GROUP BY MT.TERMEKKOD, MT.TERMEKNEV, MT.EGYSEGAR

Suppose you wanted IN for some other field that wasn't unique, let's say
MEGRENDELESEK.MYNOTUNIQUEFIELD. To avoid too many rows involved in the
sum (you only want one row of MEGRENDELESEK joined to each row of
MEGRENDELESTETELEK), you could do something like

SELECT MT.TERMEKKOD, MT.TERMEKNEV, SUM(MT.MENNYISEG) AS MENNYISEG,
SUM(MT.TELJESITETTMENNYISEG) AS TELJESITETTMENNYISEG, MT.EGYSEGAR,
SUM(MT.NETTOERTEK) AS NETTOERTEK, SUM(MT.AFAERTEK) AS AFAERTEK,
SUM(MT.BRUTTOERTEK) AS BRUTTOERTEK
FROM MEGRENDELESTETELEK MT
JOIN MEGRENDELESEK MS ON MS.MYNOTUNIQUEFIELD = MT.MYNOTUNIQUEFIELD
WHERE (MT.TIPUS=:T1 OR MT.TIPUS=:T2)
AND MS.SOMESTRANGEFIELD < 5000
AND NOT EXISTS(SELECT * FROM MEGRENDELESEK MS2
WHERE MS2.MYNOTUNIQUEFIELD = MS.MYNOTUNIQUEFIELD
AND MS2.SOMESTRANGEFIELD < 5000
AND MS2.MYGRENDELESID < MS.MYGRENDELESID)
GROUP BY MT.TERMEKKOD, MT.TERMEKNEV, MT.EGYSEGAR

The point is to only accept the lowest row of MYGRENDELESEK that match
the conditions, and that can be ascertained through comparing the
primary key: AND MS2.MYGRENDELESID < MS.MYGRENDELESID

I also included SOMESTRANGEFIELD to show you that you must include such
criteria in both the main where clause as well as the not exists subselect.

For this to work OK, you would of course require that MYNOTUNIQUEFIELD
is pretty selective and indexed, or (not equally good) that
SOMESTRANGEFIELD is selective and indexed.

Does this answer your question?
Set

Balanyi Zsolt wrote:

> Hi!
>
> I have the following two queries (FB SS 1.5.2 Win XP):
> 1)
> SELECT TERMEKKOD, TERMEKNEV, SUM(MENNYISEG) AS MENNYISEG,
> SUM(TELJESITETTMENNYISEG) AS TELJESITETTMENNYISEG, EGYSEGAR,
> SUM(NETTOERTEK) AS NETTOERTEK, SUM(AFAERTEK) AS AFAERTEK,
> SUM(BRUTTOERTEK) AS BRUTTOERTEK
> FROM MEGRENDELESTETELEK WHERE (TIPUS=:T1 OR TIPUS=:T2)
> AND MEGRENDELESID < 5000
> GROUP BY TERMEKKOD, TERMEKNEV, EGYSEGAR
>
> 2)
> SELECT TERMEKKOD, TERMEKNEV, SUM(MENNYISEG) AS MENNYISEG,
> SUM(TELJESITETTMENNYISEG) AS TELJESITETTMENNYISEG, EGYSEGAR,
> SUM(NETTOERTEK) AS NETTOERTEK, SUM(AFAERTEK) AS AFAERTEK,
> SUM(BRUTTOERTEK) AS BRUTTOERTEK
> FROM MEGRENDELESTETELEK WHERE (TIPUS=:T1 OR TIPUS=:T2)
> AND MEGRENDELESID IN (SELECT MEGRENDELESID FROM MEGRENDELESEK WHERE
> MEGRENDELESID < 5000)
> GROUP BY TERMEKKOD, TERMEKNEV, EGYSEGAR
>
> They differ only in the 5. line, but the line filters the same rows
> (where MEGRENDELESID < 5000)
> The funny thing is, that if I use query 1) then I get the plan:
> PLAN SORT ((MEGRENDELESTETELEK INDEX (FK_MEGRENDELESTETELEK)))
> where FK_MEGRENDELESTETELEK is a foreign key to table MEGRENDELESEK on
> the PK field MEGRENDELESID
> Execution time is around 1,8 sec
>
> If I use query 2), the plan is:
> PLAN (MEGRENDELESEK INDEX (PK_MEGRENDELESEK))
> PLAN SORT ((MEGRENDELESTETELEK NATURAL))
> where PK_MEGRENDELESEK is the primary key on the field MEGRENDELESID
> Execution time is around 3,5 sec
>
> Can anyone explain to me how to make plan of the query 2) to end like
> plan of qurey 1)?
>
> Best regards, Zsolt Balanyi