Hi Sugiharto!

At 13:01 01.03.2003 +0700, you wrote:

>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

It should give the same result as your LEFT JOIN and I'd assume the speed
to be about the same, but I don't know and it would be nice to know if it
made any difference.

In general, client/server databases appears to be slow when you select
entire tables rather than specific records. Always try to use as limiting a
where clause as possible and never return any redundant records (that is
the big jump from desktop databases).

HTH,
Set