Re: Hint for in memory temporary table?

Ok, so i did some research. 10g R2.

GTTs (global temporary tables) are cached in the SGA as any normal table. dbwriter writes it to disc.

If you insert append, it is directly written to disk. Then when you query, it is been read into the SGA.

If you truncate it with dirty blocks, the table is simply released without flushing.

If memory needs grows, it will be written out to disk as needed.

Still unknown reason for the cache hint, but i would think its working in a similar way to the "cache" hint for tables.

Christo Kutrovsky
Senior Database/System Administrator
The Pythian Group

On 1/16/06, Christo Kutrovsky <kutrovsky.oracle_at_gmail.com> wrote:
> Jonathan,
>
> I've tried all variants i can think off cache_temp_table()
>
> insert /*+ cache_temp_table() */ into gtt_tst t select * from other;
>
> I tried:
> cache_temp_table
> cache_temp_table(t)
> cache_temp_table(gtt_tst)
> cache_temp_table()
>
> None of which had any effect and the table was fully written in temp.
>
> Christo
>
> On 1/13/06, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
> >
> > There is a cache_temp_table() hint, but
> > I haven't done any experiments on it.
> >
> >
> > Regards
> >
> > Jonathan Lewis
> >
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> > The Co-operative Oracle Users' FAQ
> >
> > http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
> > Cost Based Oracle: Fundamentals
> >
> > http://www.jlcomp.demon.co.uk/appearances.html
> > Public Appearances - schedule updated 10th Jan 2006
> >
> > ----- Original Message -----
> > From: "Christo Kutrovsky" <kutrovsky.oracle_at_gmail.com>
> > To: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>; "oracle-l"
> > <oracle-l_at_freelists.org>
> > Sent: Friday, January 13, 2006 4:58 PM
> > Subject: Hint for in memory temporary table?
> >
> >
> > Hello,
> >
> > Do you guys remember what was the hidden/special hint to create a
> > global temporary table in memory or to force the data to stay in
> > memory?
> >
> >
> > --
> > Christo Kutrovsky
> > Senior Database/System Administrator
> > The Pythian Group
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> Christo KutrovskySenior Database/System AdministratorThe Pythian Group
>

--
Christo KutrovskySenior Database/System AdministratorThe Pythian Group
--
http://www.freelists.org/webpage/oracle-l