Re: ** temp table or permanent

Unless you use shared servers, PL/SQL tables should be stored in PGA. I don't remeber what are limitations of PL/SQL tables in 8i and performance implications so I can't help you on this one.

From the top of my head I would use global temporary table if possible as it will use direct path reads and won't consume much additional memory.

On the other hand, maybe you should step back and see if you can optimize and rewrite the query. Consider running it with 10046 event and see where you spend the time. If the query returns many rows, chances are that fetch size is too small and/or your network is too slow.

2006/7/5, A Joshi <ajoshi977_at_yahoo.com>:
> Thanks. I am on 8.1.7.4. So it is not automatic UNDO. I already split
> thequery in two. Right now the question is if I should use
> temporary/permanent table or PL/SQL table. I assume PL/SQL table will always
> be faster than temporary table. We have dedicated server so I think it is
> using memory from main memory and not affecting SGA. How do I keep track of
> how much memory is being used? I want balance performance and memory. to
> make sure no paging/swapping occurs. Thanks

-- 
Best regards,
Alex Gorbachev

http://blog.oracloid.com
--
http://www.freelists.org/webpage/oracle-l