Difference between Global temporary table (GTT) and collections

I wanted to know the difference between GTT and collections with scenarios where to use GTT and where to use collections.

Answer

Global Temporary Tables are permanent data structures. We can manipulate data in a GTT using SQL like any other table. What distinguishes them from regular heap tables is:

1. Their data is transient, and only persists for the duration of a transaction or a session (depending on definition).
2. They write their data to a temporary tablespace rather than a permanent tablespace.

The second point is something people often overlook. Writing to and reading from global temporary tables entails disk i/o. So a GTT may not be the cheap cache we think it is. Also, it’s worth making a separate temporary tablespace dedicated to GTTs, to avoid contention with other processes which use a temporary tablespace (such as disk sorts).

PL/SQL collections are in-memory session variables. Their storage allocation comes out of the Private SGA allocation – which can be quite small, depending on how the database is configured. Although we query a collection using a SQL SELECT statement with a table() function, PL/SQL collections require the use of PL/SQL to define and populate them.

So, when to use a GTT and when to use a collection?

Use a GTT when we have a frequent need to use a result in multiple different queries. This might manifest as a requirement to execute ad hoc queries over a temporary aggregation of data. I stress the frequent need: these are permanent data structures, so we only build them when we have users (which includes things such as background report jobs) which are going to repeat the populate/query/discard routine with a defined projection of data.

Use a collection when we’re writing procedural code which manipulates a small set of data (or a large set of data which can be broken up into smaller batches) where the subset is a by-product or stepping stone in the process, and not an artefact of interest in its own right. For instance, we might use collections to pass data sets between program units.

My personal experience is that collections are far more common than global temporary tables. The main definers of GTTs are probably developers with more experience of MSSQL than Oracle, who are writing translations of T-SQL rather than idiomatic Oracle code. Sometime people think they need a GTT when what they actually need is a materialized view.

Finally, following on from that point, I would like to suggest that there is much less need to use GTTs or collections in Oracle than one might think. SQL is very efficient at joining tables, and the overhead of populating and then reading GTTs is probably way higher than just executing a SQL statement. Certainly it’s worth starting with just the SELECT statement, and only considering collections or GTTs if we can’t tune the query down to an acceptable elapsed time.