Automatic commit is a side effect of issuing CREATE INDEX statement even on local temporary tables. Questions:
1. Is it really necessary (technically) on local temporary tables?
2. How could we avoid automatic commits when calling a procedure which declares local temporary tables and creates indexes on them (for performance reasons)?
I think, SA version is not important here, since all versions behave the same.
Waiting for any hints. Thanks.

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.

asked 03 Apr '14, 05:10

Arthoor's gravatar image

Arthoor
1.1k274056
accept rate: 0%

edited 03 Apr '14, 07:56


I think, SA version is not important here, since all versions behave the same.

Not fully true: v16 has introduced a changed behaviour here (and obviously an improvement from your point of view:) - cf. the docs

Default behavior has changed when creating indexes on local temporary tables In previous releases, the database server always executed a COMMIT before creating an index on a local temporary table. Now, the database server does not perform a COMMIT before creating an index on a local temporary table. You can control this behavior by setting the auto_commit_on_create_local_temp_index database option. See auto_commit_on_create_local_temp_index option.


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...

permanent link

answered 03 Apr '14, 06:03

Volker%20Barth's gravatar image

Volker Barth
30.6k307456663
accept rate: 32%

edited 03 Apr '14, 06:09

Oops, I missed that. Thanks. :) I've edited my question.
Is there a better way to check if local temporary table already exists (this could be a separate question but I don't want to flood the forum)?

...
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412
×137
×26
×4
×1

question asked: 03 Apr '14, 05:10

question was seen: 684 times

last updated: 03 Apr '14, 10:50