> 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).

Thank you for clearing this up.

> 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.

Surprisingly, it did make a difference. The result is the same, but this
version is a lot faster (2 seconds as opposed to 16-20 seconds for the
LEFT JOIN version). I expect the LEFT JOIN version is not that slow for
smaller datasets, but I'm testing this on a somewhat large tables:
- CUSTOMER : 5,000 records
- PRODUCT : 20,000 records
- SALESMAN : 1,000 records
- ORDERMASTER : 180,000 records
- ORDERDETAIL : approx. 546,000 records

> 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).

Thanks for the input. I agree that for general use, 'select *' is
inefficient, But sometimes for reporting or data warehouse queries,
'select *' is the only way to go.

Many thanks,
sugi.