Community
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

Create index on local temporary table - automatic commit

Go to solution
former_SQLA_member1694875
Participant

on ‎2014 Apr 03 6:10 AM

4,139

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.

Know the answer?

Help others by sharing your knowledge.

Answer

Need more details?

Request clarification before answering.

Show replies
Show replies
View Entire Topic
VolkerBarth
Contributor
‎2014 Apr 03 7:03 AM

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

Show replies
Show replies
former_SQLA_member1694875
Participant
‎2014 Apr 03 8:44 AM
0 Likes

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.

Breck_Carter
Participant
‎2014 Apr 03 11:50 AM
0 Likes

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

Ask a Question