Hi,

> >CASE 2 : Same as CASE 1, Change JOIN to LEFT JOIN.
> > select p.productID, sum(o.qty)
> > from product p
> > LEFT JOIN orderdetail o on (p.productid = o.productid)
> > group by p.productID
> >RESULT:
> >- PLAN SORT (JOIN (P NATURAL,O INDEX (FK_ORDERDET_REFERENCE_ITEMS)))
> >- First run: approx 32 seconds
> >- Subsequent runs: approx 20 seconds.
> >QUESTIONS:
> >1. I read somewhere in this list that any occurence of 'NATURAL' in a
> >plan means that the query is not optimized. Is it the case here?
> >2. The only difference with CASE 1 is the left join, but the performance
> >drops is unacceptable in this case. Is there anything I can do to speed
> >this up?
>
> There is nothing wrong with NATURAL in your plan, since you do not specify
> any WHERE clause, NATURAL is the quickest plan it can possibly use for the
> first table. It is far worse if NATURAL occurs in subsequent tables in the
> plan (e.g. you should definitely try to avoid NATURAL for O).
>
> What could be worth trying, is
>
> select distinct p.productID, (select sum(o.qty) from orderdetail o

where

> p.productid = o.productid)) AS Sum_Qty
> from product p

I think in FB1 this query doesn't work, maybe the next will.
In FB1.5 both will work.

SELECT
p.productID,
(select sum(o.qty) from orderdetail o where p.productid = o.productid)) AS
Sum_Qty
FROM
product p
GROUP BY
p.productID

Regards,
Arno Brinkman