RE: ** temp table or permanent

[A Joshi <ajoshi977@xxxxxxxxx>]

Igor,
    Thanks. got it. I think GTT (global temporary table) is better from memory 
point of view since it will be kept out of memory if it is too big. So I do not 
understand : What are the advantages of PL/SQL table over temproary table. if 
any. Thanks

Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote:
      If GTT fits in memory (no physical IO), why should it be slower than 
PL/SQL table?
   
  Igor

    
---------------------------------
  From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of A Joshi
Sent: Thursday, July 06, 2006 9:14 AM
To: pythianbrinsmead@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: ** temp table or permanent


  
  Mark,
    Yes. Optimize/rewrite query was done. Then it was split up.  Iam on 8.1.7 
so no UNDO and I cannot increase the rolback segment any more. apart from 
storgae and memory, I think there is a performance impact too if I increase the 
size of rllback segments. 
     So now the question is whether global temporary table or PL/SQL table. I 
assume PL/SQL table is always faster than temporary table. 
     I am avoiding permament table or materialized view since that will involve 
redo/undo and increase the time. Thanks for your help.

Mark Brinsmead <pythianbrinsmead@xxxxxxxxx> wrote:
  You seem to be jumping through a lot of hoops to solve a fairly 
straight-forward (sounding) problem.

Your first step should be to optimize / rewrite the query to perform less I/O 
and or execute more quickly.

Your second step should be to increase UNDO / Rollback as needed, within 
practical limits, of course.

After this, you might consider temporary tables (Global Temporary Table 
recommended) etc. or 
Materialized Views. 

Of course, advice like this is easy to give when I know *nothing* of your 
specific situation.  ;-)


  On 7/5/06, A Joshi < ajoshi977@xxxxxxxxx> wrote:       Hi,
     I had a big query which was running out of rollback so after some tuning i 
had to break it down in to two. from the first query i store the result in a 
temporary table and then use it for second part. it is still slow. i did not 
use permament table since that could involve rollback/redo. but still i will 
try to check it. i am also thinking of pl/sql table but if that always remains 
in memory then it could impact memory or sga. pl/sql table uses pga or OS 
memory but if paging swapping occurs then it would impact sga too. Will it 
always be faster than temp or permanent table? Can some one give feedback if 
you had similar experience. Thanks for help. 

    





-- 
Cheers,
-- Mark Brinsmead
   Staff DBA,
   The Pythian Group
   http://www.pythian.com/blogs 
    
---------------------------------
  Do you Yahoo!?
Next-gen email? Have it all with the all-new Yahoo! Mail Beta. 

                
---------------------------------
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls.  Great rates 
starting at 1&cent;/min.

• References:
  • RE: ** temp table or permanent
    • From: Igor Neyman

Other related posts:

** temp table or permanent

Re: ** temp table or permanent

Re: ** temp table or permanent

Re: ** temp table or permanent

Re: ** temp table or permanent

Re: ** temp table or permanent

RE: ** temp table or permanent

RE: ** temp table or permanent

RE: ** temp table or permanent