Skip to Main Content
  • Questions
  • collections vs Global temporary variables

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, venkat.

Asked: March 08, 2016 - 12:31 pm UTC

Last updated: March 09, 2016 - 9:52 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Any one suggest me .
Collections vs Global Temporary Tables ? Which one is better in performance wise ?In which situation go for collections and Global temporary tables.

and Chris said...

Both are useful for storing working data. But they work in very different ways.

Collections

- Can use bulk processing (forall)
- Useful for returning arrays to the client
- Size limited by memory

Temporary tables

- Works like a normal table in SQL
- Can create indexes on the table
- Modifying the contents generates undo/redo
- Size limited by disk

There are improvements to temporary tables in 12c, notably session private stats:

https://oracle-base.com/articles/12c/session-private-statistics-for-global-temporary-tables-12cr1

and temporary undo:

https://oracle-base.com/articles/12c/temporary-undo-12cr1

There are also improvements to collections in 12c. For example, you can now use associative arrays in SQL with the table() operator:

http://www.oraclefrontovik.com/2014/08/using-sql-with-associative-arrays-of-records-in-oracle-12c/

Which is better ultimately depends upon what you're doing.

Mostly doing PL/SQL processing on your data? Collections are a better bet.
Plan on using the data in SQL? Temporary tables may be the way to go.

Which performs better varies case-by-case. Build a test case for your situation and try it out!

Note that collections come in three flavours:

- Associative arrays
- Nested tables
- Varrays

Each of these has their own pros and cons. The documentation gives a thorough rundown of these differences:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS99931

Is this answer out of date? If it is, please let us know via a Comment