At 03:52 PM 19-06-02 +0300, you wrote:

> >>
> >>update table1
> >> set total = (select sum(Quantity) from table2 where table2.CustomerID
> >>= table1.CustomerID)
> >>
> >>CustomerID is a foreign key for both tables, references to the same
> >>table "Customers".
> >>That operator hangs because table2 is large and engine uses natural
> >>joins. How to work arround the problem?
> >>
> >>
>Martijn Tonies wrote
> >What is the query plan?
> >
>You wrote
> >
>PLAN (T2 NATURAL)
>PLAN (T1 NATURAL)

There is no join involved here. The query is slow because it is forming a
separate aggregate for each row in table 1. If you are using Firebird, it
may also be syntactically incorrect. Try

update table1 t1
set total = (select sum(t2.Quantity) from table2 t2
where t2.CustomerID = t1.CustomerID)

However, a join might be faster, since it ought to use both of the
RDB$INTEGn indexes on the two CustomerID columns for the join and get your
output faster than grinding through the two tables in natural order, viz.

update table1 t1
set total = (select sum(t2.Quantity) from table2 t2
join table1 t3
on t2.CustomerID = t3.CustomerID
where t2.CustomerID = t1.CustomerID)

heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________