Select from Final table performance implication
db2 add tag
Anonymous 795 
I have a question about this fiddle:

<>https://dbfiddle.uk?rdbms=db2_11.1&fiddle=d5d2f86597ebdbda42e9b17a778b3e01

What are the performance implications of using "select * from final table" over separate Update and Select statements if the table is large with (millions or hundreds of millions of records) and updates on individual records in the range of 100s of thousands or millions, assuming primary is and index are defined properly ?
Top Answer
Lennart Jonsson 
One should never say never, but in general I would expect `SELECT .. FROM FINAL TABLE ( UPDATE ...)` to perform better than individual UPDATE, SELECT statements. 

I believe (though IBM removed the blogs so I can't verify) that the construction was born as part of a TPC benchmark record attempt as a mean to optimize performance.
comments
transcript

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.