Home » SQL & PL/SQL » SQL & PL/SQL » Global temp table Vs Nested table.
Global temp table Vs Nested table. [message #202331] Thu, 09 November 2006 02:58 Go to next message
Messages: 63
Registered: May 2006
What are the advantages of using NESTED TABLE instead of GLOBAL TEMPORARY TABLE?


For each session I require to use something to hold data specific to that session.


1. I can use global temporary table.


2. I can create a OBJECT TYPE/NESTED TABLE in database and declare NESTED TABLE of that type in package specification.

Functionality wise both approach are same. I would like to know which approach would be better and why?

Waiting for expert comments.


Re: Global temp table Vs Nested table. [message #202336 is a reply to message #202331] Thu, 09 November 2006 03:25 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It depends what you want to do with the data, and how much data you're planning on having.


Access to data held in a package variable is substantially quicker than access to data held in a table.

Global tables are much easier to inclide in SQL statements

GTTs can hold a very large amount of data, but if you've got a lot of sessions and they're all holding a lot of data in pacakge variables, you can start using a LOT of memory.

Re: Global temp table Vs Nested table. [message #202487 is a reply to message #202336] Fri, 10 November 2006 00:36 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What do you want to want to do with the data once you've got it?

Use a Nested Table (with BULK COLLECT and LIMIT clause if possible) if you want to manipulate the data procedurally with lots of IF/THENs and the like.

Using BULK COLLECT with the LIMIT clause can be a good way to work-around the memory problem.

Ross Leishman
Previous Topic: Sample Data model .
Next Topic: ref cursor to return boolean value
Goto Forum:

Current Time: Wed Aug 23 12:44:17 CDT 2017

Total time taken to generate the page: 0.10166 seconds