Create index on local temporary table - automatic commit

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
1.3k●35●52●66
accept rate: 11%

edited 03 Apr '14, 07:56

flat view

One Answer:

active answers oldest answers newest answers popular answers

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 Barth
39.3k●354●538●808
accept rate: 33%

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

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

sa-12 ×438
sa-11 ×143
index ×29
commit ×7
side-effect ×1

question asked: 03 Apr '14, 05:10

question was seen: 2,665 times

last updated: 03 Apr '14, 10:50

SAP SQL Anywhere

Create index on local temporary table - automatic commit

Follow this question

Related questions