Re: Stored proc/ORDER BY question.

Michael Benveniste wrote: > > I have roughly the following SQL in a stored procedure: > > FOREACH > SELECT VAL1, VAL2, VAL3 > INTO RES1, RES2, RES3 > FROM ATABLE > WHERE VAL1 = whatever > ORDER BY VAL3 > [blah blah blah] > END FOREACH;> > Now, I assume that the SELECT only executes and saves > its results in a temporary area. Now my questions. Does > it save a copy of each record in the temporary area, or > does it save some sort of temporary index pointing to the > data? Assuming no transactions or overt locking, is this > construct guarenteed to return sequential values of VAL3, > or could it return a non-sequential value if another process > updates the record while processing a previous one? The answer will depend on your isolation level and whether an actual physical sort took place. If SET EXPLAIN says that a temp table was required for ORDER BY then the data is being returned to you from a temp table and the view you see will be consistent until the transaction completes no matter what happens to the actual rows. If not then your database's log mode and current isolation level (related) determine what happens if someone updates inserts or deletes rows in the table. Check out the manuals for details. Art S. Kagel