Re: Temporary Tables - 3 of 3

The main disadvantage with global temporary tables, is that you cannot gather statistics for them (unless a hint is provided), and hence, the optimizer might not choose an optimal plan, especially when you join GTT's with permanent tables in your transactions. Second, is that if you have too many sessions accessing the GTT, then since each session gets its own copy of the table, you might end up occupying too much temp space.

                                                                                                                                        
                      rjamya                                                                                                            
                      <rjamya_at_gmail.com>        To:       Igor Neyman <ineyman_at_perceptron.com>                                          
                      Sent by:                  cc:       gkatteri_at_fastmail.fm, Meenakshi.Aggarwal_at_fishersci.com, oracle-l              
                      oracle-l-bounce_at_fr         <oracle-l_at_freelists.org>                                                               
                      eelists.org               Subject:  Re: Temporary Tables - 3 of 3                                                 
                                                                                                                                        
                                                                                                                                        
                      08/26/2005 03:03                                                                                                  
                      PM                                                                                                                
                      Please respond to                                                                                                 
                      rjamya                                                                                                            
                                                                                                                                        
                                                                                                                                        

if it is solely because of memory processing being faster, then we should be using in-memory databases. I like GTTs because they provide more flexibility, if I am using 10 rows or 10m rows. It is just like another table ... no casting required.

Raj

On 8/26/05, Igor Neyman <ineyman_at_perceptron.com> wrote: Temporary tables will be also "in memory" if they fit sort_area. And, "on commit preserve/delete" only adds flexibility to temporary table, which speaks in their favor.

Igor Neyman

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of GovindanK Sent: Friday, August 26, 2005 2:16 PM
To: rjamya
Cc: Meenakshi.Aggarwal_at_fishersci.com; oracle-l Subject: Re: Temporary Tables - 3 of 3

Memory processing is any time faster than table handling. Lesser maintenance.
As i have mentioned in 1 of 3 and 2 of 3 one need not have to bother about on commit preserve/delete.
You can select multiple times within the same session and the values are avbl to you.

HTH
GovindanK

On Fri, 26 Aug 2005 12:41:05 -0400, "rjamya" <rjamya_at_gmail.com> said:
> Govindan, interesting observation, would you elaborate why you'd
> choose object over GTT ??
>
> Raj
>
> On 8/26/05, GovindanK <gkatteri_at_fastmail.fm> wrote:
> >
> > If it does not involve too much of volume i would prefer creating an

> > object type, assiging values to it and select CAST from it.
> >
> > HTH
> > GovindanK
> >
> > On Fri, 26 Aug 2005 11:52:23 -0400, "Aggarwal, Meenakshi"
> > <Meenakshi.Aggarwal_at_fishersci.com> said:
> > > Does anybody know any reason of not using temporary tables in
Oracle 9i.
> > >
> > > Thanks
> > > --
> > > http://www.freelists.org/webpage/oracle-l
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>
>
>
> --
> ------------------------------
> select standard_disclaimer from company_requirements where category =
> 'MANDATORY';

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




--
------------------------------
select standard_disclaimer from company_requirements where category =
'MANDATORY';


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