Re: What happens here ?

In article <521_at_marlene.GUN.de>, @marlene.GUN.de (Andrew Renney) writes:
|>open write transaction
|>declare statement x as "select * from xyz where x = 1000"
|>open cursor from statement x
|>delete from xyz where x = 1000
|>fetch cursor
|>while (found)
|> modify record
|> insert record
|> fetch cursor
|>end while
|>close transaction

Andrew,

You are correct in that Rdb will fetch at least one row and buffer it when you OPEN the cursor. If the cursor involves an access strategy that requires sorting or aggregation then it may buffer the entire result set.

Adding an ORDER BY to the cursor on a column not in an index will most likely cause the entire table to be buffered.

I assume the 'modify record' is modifying the contents in memory after the fetch. Wouldn't it make sense to do an UPDATE instead of this construct for both Oracle7 and Rdb. Certainly in Rdb an UPDATE in place is cheaper than a delete then a insert.

update xyz
set ...
where x = 1000;

(even in dynamic) is much easier to write than using a dynamic cursor.

Ian

(Standard disclaimer: These opinions are mine and in no way represent a commitment or opinion of my employer) Received on Fri Sep 09 1994 - 22:26:35 CEST