Oracle SQL Performance tuning: Temp Tables Vs Table Variables

There has been a debate ever since the creation of both temp tables and table variables - which of them should be used in a given situation? Ever since their introduction, the key to finding an answer has been the same - knowing the specifics in the instance and correlating them with what each of these two offers. 

In this article, we will discuss the nature of both these entities and how they can help during Oracle SQL performance tuning, as well as the major differences between them.

Temp Tables & Table Variables in Oracle SQL Performance Tuning

A temp table is simply a basic SQL table, but it is defined and contained in TempDB. The sole distinction between a temp table and a permanent one is that temp tables cannot own foreign keys. Users can view one by using the Expanding Temp Tables function in TempDB.

There are a few specific differences between them and table variables, which we shall touch upon below.

• Scope

A user can define a temp table two ways - locally and globally. Locally defined tables last through a session and can be utilized by anything being executed during the same, whereas globally defined temp tables can be used throughout the database, as discussed during SQL performance tuning as well. 

For instance, if a temp table is created in a certain procedure X, and procedure X calls to pro. Y, then both X and Y can use this temp table. However, once the particular session is over, the temp tables defined and allocated during the session are automatically unassigned.

A temp table that has been defined globally is only deallocated after the originating sessions have been terminated, and every lock in use until then has been released. Syntactically, it is created by applying the “#” twice. 

Conversely, a user can only define a table variable locally and their scope is also restricted to the batch that they are defined in. This means you cannot reference a table variable through a different procedure during Oracle SQL performance tuning or otherwise, even though it is from the same session. 

• Performance

When the two are compared to each other in terms of performance, many DBAs have noted that temp tables fare better generally. This is because the query optimizer included in the database management system may create poor plans at times, something observed in situations with large amounts of data. 

This has led to the creation of an informal rule of thumb - for rows less than a thousand in number, a table variable is recommended, and for any number more than that, you should use a temp table. However, although it is a good thumb rule, it may not necessarily be applicable for every situation. 

In the end, it is important to understand while performing SQL performance tuning that execution plans for table variables are more likely to bear unpredictable outcomes, as opposed to those generated for temp tables. The latter, on the other hand, will generate more recompilation. 

• Recompilation

This is where the table variable becomes an advantage for the user. Recompilation can pose an obstacle when coding for sub 2nd throughput. This is due to the fact that the size of the dataset tends to change enough to require recompilation. 

Ultimately, the answer remains the same, when we compare a temp table to a table variable. If you are sure about the source data definitions and only small result sets are involved, then table variables are ideal. Otherwise, temp tables are likely to be sufficient.