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
ramesh_samane
Messages: 63
Registered: May 2006
Member
What are the advantages of using NESTED TABLE instead of GLOBAL TEMPORARY TABLE?

Scenario:

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

Solution:

1. I can use global temporary table.

OR

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.

Thanks,

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

eg,

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
rleishman
Messages: 3724
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: executeQuery vs. executeUpdate on a resultset
Next Topic: GetLine Number of Error in PL/SQL Proc
Goto Forum:
  


Current Time: Wed Dec 07 22:14:28 CST 2016

Total time taken to generate the page: 0.08639 seconds