OraFAQ Forum: SQL & PL/SQL » Difference between Global Temporary Table and Collections in PLSQL

Home » SQL & PL/SQL » SQL & PL/SQL » Difference between Global Temporary Table and Collections in PLSQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference between Global Temporary Table and Collections in PLSQL [message #651670]

Mon, 23 May 2016 02:02

msyogi
Messages: 16
Registered: May 2016
Location: Hyderabad

Junior Member

What are the differences between Global Temporary Table and Collections in PLSQL. And which is better in case of performance?
And please let me know when to use these?

Report message to a moderator

Re: Difference between Global Temporary Table and Collections in PLSQL [message #651671 is a reply to message #651670]

Mon, 23 May 2016 02:16

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

One is stored on temporary segment, the other one is in memory.
Most of the time you have not to use them.

See Temporary Tables and PL/SQL Collections and Records.

Report message to a moderator

Re: Difference between Global Temporary Table and Collections in PLSQL [message #651672 is a reply to message #651671]

Mon, 23 May 2016 02:19

msyogi
Messages: 16
Registered: May 2016
Location: Hyderabad

Junior Member

So Any idea which is better among those two?

Report message to a moderator

Re: Difference between Global Temporary Table and Collections in PLSQL [message #651673 is a reply to message #651672]

Mon, 23 May 2016 02:23

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

None is better in all cases (otherwise the other one would not exist), it depends on what you have to do.
Read the links I gave you.

Report message to a moderator

Re: Difference between Global Temporary Table and Collections in PLSQL [message #651676 is a reply to message #651670]

Mon, 23 May 2016 02:48

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I'll attach a script I use in class to demonstrate some differences between the two. It may help you decide which is better depending on what you want to achieve.

Attachment: associative_array_GTT_demo.txt
(Size: 1.35KB, Downloaded 1473 times)

Report message to a moderator

Re: Difference between Global Temporary Table and Collections in PLSQL [message #651701 is a reply to message #651672]

Mon, 23 May 2016 06:43

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

msyogi wrote on Mon, 23 May 2016 02:19

So Any idea which is better among those two?

What is better, a hammer or a screwdriver?

Report message to a moderator

Re: Difference between Global Temporary Table and Collections in PLSQL [message #651753 is a reply to message #651701]

Tue, 24 May 2016 08:30

Bill B
Messages: 1971
Registered: December 2004

Senior Member

a GTT is a pseudo table that is stored in the temp tablespace and can be manipulated by using standard oracle commands like select, update, delete. It can also have indexes applied. Anything you can do with a normal table you can do with a GTT Except all data in the table is ONLY seen by the specific session and no one else can see or manipulate your data.

A collection is just a memory array that needs to be accessed using a different set of instructions.

I use GTT's a fair amount, I also use collections. Both have their strengths and weaknesses.

Report message to a moderator

Previous Topic:	Moving data older than 2 years in all fact tables to respective archive tables & purging data
Next Topic:	first tuesday of every month ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 10:30:15 CDT 2024