Automatic commit is a side effect of issuing CREATE INDEX statement even on local temporary tables. Questions: Edited: Following Volker's answer, my primary statement that all versions behave the same was incorrect. So I'm currently more interested in SA11 and SA12. |
Not fully true: v16 has introduced a changed behaviour here (and obviously an improvement from your point of view:) - cf. the docs
That being said, it seems not possibly to omit the automatic commit in v12 and below - you might only re-organize your code to pre-create the temporary table and index before the stored procedure is called and truncate the table afterwards. I am not sure whether that would lead to a worse performance... Oops, I missed that. Thanks. :) I've edited my question. ... begin select 1 into @a from #tmp; exception when others then ... //create temp. table and index end ... This is what we should check in the procedure if we pre-create temporary tables somewhere outside since the procedure can be called from different places and pre-creation would take place only where the logic is sensitive to that side effect.
(03 Apr '14, 07:44)
Arthoor
Replies hidden
> this could be a separate question but I don't want to flood the forum Separate questions should always be separate questions, never continuations of another discussion. Flooding is not an issue.
(03 Apr '14, 10:50)
Breck Carter
|