The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.1k264056
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
29.3k287438644
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:

×404
×137
×25
×3
×1

question asked: 03 Apr '14, 05:10

question was seen: 566 times

last updated: 03 Apr '14, 10:50