RE: problem about full scan temporary table

Thanks for your reply, and I have a question based on your answer.

How does oracle know certain session will insert how much data into the GTT?
Suppose the session first inserts 1 row, and 10 minutes later, inserts
another 1,000,000 rows. So at the beginning to operate the GTT, how much
space will be allocated? If the space one session occupied is not
continuous, then I don't think HWM makes much sense.

Thanks
Qihua
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Sunday, September 24, 2006 3:31 PM To: oracle-l_at_freelists.org
Subject: Re: problem about full scan temporary table

Every session gets its private copy of the table in the temporary tablespace (make sure it's big enough), and knows its own HWM.

If you are using 10g, consider using tablespace groups - just in case it gives you an edge in performance, and in case you need to drop and recreate some temporary files.

If you are running 9i, "on commit delete rows" may be a better bet than "on commit preserve rows". If you TRUNCATE a GTT, that counts as DDL which invalidates every cursor that references that GTT definition - even the cursors of people with their own private data sets. Fixed in 10.2. Conversely, the 'commit' if you have 'on commit delete rows' is not DDL, so doesn't invalidate anything. The whole thing may be a moot point, of course, as the impact on the library cache may be insignificant compared to the work done in lots of high-volume tablescans.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

• Original Message ----- From: "qihua wu" <wqhhp_at_yahoo.com> To: <oracle-l_at_freelists.org> Sent: Sunday, September 24, 2006 7:12 AM Subject: problem about full scan temporary table

> Hi, all
>
> I created a global temporary table, and many
sessions
> will populate huge
> volume of data into this table. All of these
sessions
> will access the table
> by means of full scan. Suppose session S1 inserted
> 10,000 blocks of data,
> and S2 inserted 10,000 blocks of data. If S1 scan
the
> table, will it scan
> 10,000 blocks or 20,000 blocks? For normal table,
> there is high water mark
> that determine the upper boundary that oracle will
> scan, for temporary
> table, is there a similar high water mark for each
> session, or for all
> session?
>
> Thanks
> Robin
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam
protection around
> http://mail.yahoo.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.405 / Virus Database: 268.12.8/455 -
Release Date: 22/09/2006
>
>

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





__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
--
http://www.freelists.org/webpage/oracle-l