RE: Simplest way to create/use PLSQL collections

Yes that was one of the issues, wasn't there something else with temporary tables generating a bunch of REDO?

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman Sent: Wednesday, May 18, 2005 10:48 AM
To: oracle-l_at_freelists.org
Subject: 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

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