Re: Global temporary tables v pl/sql tables

"Ryan" <rgaffuri_at_cox.net> wrote in message news:cpoW8.68471$DB.1742984_at_news1.east.cox.net...
> What about performance comparisons? I know that in some cases pl/sql
tables
> are useful if you need to do a series of joins and /or sorts you can load
> one table into memory by putting it in a pl/sql table.

Exactly. The data is all stored in memory and hence can be reaccessed very effeciently. The issue of course is do you have sufficient memory available. Or would it be useful to access this data multiple times from multiple pieces or executions of code, etc. Then a GTT could be useful.

>
> How is the data in a GTT stored? I would assume there is no redo and if
> there is no redo, DBWR does not write it to the data file. So is it stored
> in memory and in the temporary tablespace?

Basically yes. There is no redo but there could be I/Os to the temp files. Basically each session updating the GTT is using their own temp segments to store the data. That's how Oracle keeps it hidden from each other. Because you use disk space, you are not so restricted with the volumne of data you wish to insert into it. And because it uses "real" rows there are rowids that can be indexed etc. So accessing a subset of the data can be efficient from that point of view as well.

From an "efficency"point of view, if it's appropraite and the various restrictions of using PL/SQL tables are not an issue then it would probably be the more efficient. But the correct answer is that it really does depend on your application and what you are trying to do that ultimately determines which is more appropraite. They really are two different fish in the same ocean.

Regards

Richard

>
>
> "Keld Nielsen" <keldnielsen_at_image.dk> wrote in message
> news:M8iW8.157$s76.32298_at_news010.worldonline.dk...
> > Hi Richard,
> > Just a small comment (embedded)
> >
> > "Richard Foote" <richard.foote_at_bigpond.com> wrote
> > in message
> > news:TsgW8.30694$Hj3.92875_at_newsfeeds.bigpond.com..
> > .
> > > Hi
> > >
> > > Just to add to what Robert has said.
> > >
> > > A GTT is actually quite permanent, it's the data
> > within it that's temporary
> > > (either at a transaction or session level).
> > Therefore you can potentially
> > > access data in a GTT through many PL/SQL block
> > executions.
> > >
> > > A PL/SQL table is temporary, period, as it's a
> > memory structure that exists
> > > only for the life of the PL/SQL block.
> >
> > if it's created in a package body (or spec) it
> > will exist during
> > the life of a session - or am I wrong here ?
> >
> > >
> > > Therefore it depends on what you're trying to
> > achieve.
> > >
> > > Horses for courses and that sort of thing.
> > >
> > > Richard
> > >
> > >
> > >
> > >
> > > "R Chin" <rchin_at_panix.com> wrote in message
> > > news:agc37s$cts$2_at_reader2.panix.com...
> > > > They are different objects...
> > > > PL/SQL Table = memory structure, like array,
> > can't use SQL...
> > > > GTT = real db table that's wriiten to disk,
> > got rowids.... any DML...you
> > > > know what to do
> > > > with ordinary table you kno what to do with
> > GTT.
> > > > All depends on your needs...you use what you
> > need to do what you want to
> > > do.
> > > >
> > > > Robert
> > > >
> > > >
> > > > Tony Crispin wrote in message ...
> > > > >I am looking for a bit of advice/information:
> > > > >
> > > > >I need to understand the performance
> > implications of using either global
> > > > >temporary tables or pl/sql tables for storing
> > temporary data generated
> > > > >during a session.
> > > > >
> > > > >
> > > > >How are pl/sql tables implemented by Oracle;
> > > > >What is the benefit of using one method
> > rather than the other;
> > > > >When should one method be used rather than
> > the other?
> > > > >
> > > > >Thanks in advance
> > > > >Tony
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Mon Jul 08 2002 - 23:44:52 CDT