Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql tables versus global temporary tables
pl/sql tables versus global temporary tables [message #230216] Wed, 11 April 2007 04:18 Go to next message
khushi2000
Messages: 29
Registered: April 2007
Junior Member
Hi,

Please tell me scenarios where PL/SQL table would be advantageous than a global temporary table and vice versa.

Thanks
khushi

Re: pl/sql tables versus global temporary tables [message #230252 is a reply to message #230216] Wed, 11 April 2007 05:22 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I don't think there are any great performance advantages of one over the other. The only advantage of global temporary tables is that you could add indexes to them and they would be easier to query from outside the procedure, thereby making debugging much easier.
Re: pl/sql tables versus global temporary tables [message #230257 is a reply to message #230252] Wed, 11 April 2007 05:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Pl/Sql tables exist in memory, and accessing them is blindingly fast compared to accessing a Table.
This does mean that once you start to put a lot of rows into them, your memory usage can go up a lot.
Re: pl/sql tables versus global temporary tables [message #230270 is a reply to message #230216] Wed, 11 April 2007 06:47 Go to previous messageGo to next message
khushi2000
Messages: 29
Registered: April 2007
Junior Member
Thanks.. Can PL/SQL tables be accessed from outside ?? from a program unit other than where it is declared within the session?
Re: pl/sql tables versus global temporary tables [message #230272 is a reply to message #230270] Wed, 11 April 2007 06:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Pl/Sql tables are like any other data type. If you define them at a package level, they will persist for the duration of your session, and if you define them in a package spec, or create code to access the table then other packages can access the data in the table.
Re: pl/sql tables versus global temporary tables [message #230300 is a reply to message #230272] Wed, 11 April 2007 08:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
True, but be very careful with declaring these as public. Could result in very messy code, since it will be hard to track who (as in which program unit) changed the contents of your table
Re: pl/sql tables versus global temporary tables [message #230525 is a reply to message #230300] Thu, 12 April 2007 03:08 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
PL/SQL tables are designed for access with PL/SQL commands (eg. FIRST, NEXT, and using index subscript). They are very practical when you want to interleave access to the table rows with other PL/SQL commands because you don't have to context-switch over to the SQL engine.

The only access to GTTs is via the SQL engine. From PL/SQL this requires a context switch. You would use GTTs when you want to perform a small number of set-based operations without interleaving PL/SQL commands.

Nested Table Object Types are a kind of hybrid solution. Pretty much the same as PL/SQL tables, but you can also access them with SQL at the overhead of casting the collection to what is effectively a temporary table.

Ross Leishman
Previous Topic: select from table
Next Topic: Records not getting inserted
Goto Forum:
  


Current Time: Wed Dec 07 05:02:51 CST 2016

Total time taken to generate the page: 0.27415 seconds