Re: Simplest way to create/use PLSQL collections

Ethan,

The only general issue (of which I'm aware) with GTT's is "don't TRUNCATE if you really don't need to" or on the flip-side "only TRUNCATE infrequently and when you absolutely must".

Although the TRUNCATE TABLE command acts only on the calling session's private set of data (not affecting other sessions' private sets of data), I understand that the operation of TRUNCATE TABLE still grabs locks just as TRUNCATE on "regular" tables, and that can really slow things down in a high-concurrency situation.

If it becomes necessary to clear a GTT prior to end-of-transaction or end-of-session (which ever is the defined duration of data), then it seems best to simply use a DELETE statement.

GTTs get used internally quite a lot, more than people think, so they aren't quite so delicate as one may suspect. Examples include DBMS_STATS.GATHER_xxx_STATS, star transformations, etc...

Is anyone else aware of any general issues with GTTs?

Thanks!

-Tim

on 5/18/05 8:42 AM, Post, Ethan at Ethan.Post_at_ps.net wrote:

> Tim,
>
> I recall some bugs in 9i related to use of TEMPORARY tables that have me
> in a "be careful" mindset, I would have to go back and find the bugs to
> recall why I am thinking this but maybe someone else knows what I am
> talking about.
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
> Sent: Tuesday, May 17, 2005 10:14 PM
> To: oracle-l_at_freelists.org
> Subject: Re: Simplest way to create/use PLSQL collections
>
> Ranko,
>
> If your "browsing" has any degree of complexity, then instead of writing
> extensive PL/SQL to "browse" arrays, why not create a GLOBAL TEMPORARY
> table
> and insert rows into it and "browse" them with SELECT statements? You
> can
> set them up to clean themselves up after a COMMIT/ROLLBACK (i.e. end of
> transaction) or when your database session disconnects. A global
> temporary
> table is essentially an extension of a session's private memory.
>
> Do away with all that PL/SQL code and do it in SQL instead?
>
> Just my $0.02...
>
> -Tim
>

--
http://www.freelists.org/webpage/oracle-l