Question Re: Create index on local temporary table - automatic commit in Technology Q&A

Create index on local temporary table - automatic commit

former_SQLA_member1694875 — Thu, 03 Apr 2014 05:10:49 GMT

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.

Re: Create index on local temporary table - automatic commit

VolkerBarth — Thu, 03 Apr 2014 06:03:45 GMT

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

Re: Create index on local temporary table - automatic commit

former_SQLA_member1694875 — Thu, 03 Apr 2014 07:44:49 GMT

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.

Re: Create index on local temporary table - automatic commit

Breck_Carter — Thu, 03 Apr 2014 10:50:30 GMT

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