RE: TEMPORARY TABLE
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: 'rjamya' <rjamya@xxxxxxxxx>, "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- Date: Wed, 27 Oct 2004 14:57:53 -0400
Raja,
We don't even do delete's on this table - nothing. Oracle cleans it up for
us at the end of each transaction.
So I'd say you are doing extra work.
And what kind of statistics do we expect the optimizer to use on these
tables? It is a memory scratch-pad. I don't have any indexes on the table.
So I expect Oracle do perform full-table scans on this thing.
Maybe I'm lucky. It's working just fine for me.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: rjamya [mailto:rjamya@xxxxxxxxx]
Sent: Wednesday, October 27, 2004 2:52 PM
To: thomas.mercadante@xxxxxxxxxxxxxxxxx
Cc: oracledbam@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: TEMPORARY TABLE
I have seen lots of code that were doign truncates ... you know
just-to-be-safe. Luckily we found this in devl (and I had heard about this
issue of truncates and gtt very recently then). We mamaged to convince
development to replace truncate to delete and the world rested easily.
and by dynamic sampling ... since you can't collect statistics on GTTs, it
is preferable to use dynamic sampling, which might be a tad better than
oracle's default values for stats. Jonathan Lewis gave a very very
interesting talk on this topic at Hotsos'04.
Raj
On Wed, 27 Oct 2004 14:41:58 -0400, Mercadante, Thomas F
<thomas.mercadante@xxxxxxxxxxxxxxxxx> wrote:
> Raj,
>
> Why would you want to do truncates on these tables? By definition,
> nothing is saved in these tables when the session ends. So you never
> need to perform a truncate.
>
> I do nothing special with this table, at is is used constantly by lots
> of people all day long. I never perform a truncate on this table.
>
>
>
> Tom Mercadante
> Oracle Certified Professional
>
> -----Original Message-----
> From: rjamya [mailto:rjamya@xxxxxxxxx]
> Sent: Wednesday, October 27, 2004 2:31 PM
> To: thomas.mercadante@xxxxxxxxxxxxxxxxx
> Cc: oracledbam@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> Subject: Re: TEMPORARY TABLE
>
> well tom is almost 99% there ... there is the rest of the story ...
> about GTTs that
>
> 1. you better use dynamic_sampling with them
> 2. any even one session is useing it, you can't perform most DDLs 3.
> forget doing truncates on them, KGopal probably has the proof.
>
> otherweise they work almost as advertised ...
> Raj
>
> On Wed, 27 Oct 2004 14:20:03 -0400, Mercadante, Thomas F
> <thomas.mercadante@xxxxxxxxxxxxxxxxx> wrote:
> > Seema,
> >
> > I use Global Temporary Tables all the time. They are memory
> > structures. Very fast. Very easy to set up. No problems at all.
> >
> > Good Luck
> >
> > Tom Mercadante
> > Oracle Certified Professional
> >
> >
> >
> >
> > -----Original Message-----
> > From: Seema Singh [mailto:oracledbam@xxxxxxxxxxx]
> > Sent: Wednesday, October 27, 2004 1:34 PM
> > To: oracle-l@xxxxxxxxxxxxx
> > Subject: TEMPORARY TABLE
> >
> > Hi,
> > Is any performance problem if we go with TEMPORARY table? any
> > thoughtS? thx
> >
> > _________________________________________________________________
> > Express yourself instantly with MSN Messenger! Download today - it's
> > FREE!
> > http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>
> --
> ------------------------------
> --
> //www.freelists.org/webpage/oracle-l
>
--
------------------------------
--
//www.freelists.org/webpage/oracle-l
Other related posts:
