"David Johnson" <d_johnson@...> wrote:

>
> DB2 handles this by performing the select first, then inserting after
> all records have been selected.

And this is what the SQL spec explicitly requires:

13) Let QT be the table specified by the <query expression>
...
5) QT is effectively evaluated before insertion of any rows into T.
6) Let Q be the result of evaluating QT.
7) For each row R of Q:
a) A candidate row of T is effectively created

> 1. open cursor for "select * from a where a.t_val in (select t_val from
> t)"

And perform a full fetch from the cursor into some temporary space.
Generally, an open cursor doesn't mean it's materialized.

> This has the downside that it can be very memory intensive.

Exactly. And this is a major downside.


Dmitry