Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: problem about full scan temporary table

RE: problem about full scan temporary table

From: qihua wu <>
Date: Sun, 24 Sep 2006 03:36:23 -0700 (PDT)
Message-ID: <>

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.

-----Original Message-----
On Behalf Of Jonathan Lewis
Sent: Sunday, September 24, 2006 3:31 PM To:
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.


Jonathan Lewis

The Co-operative Oracle Users' FAQ

Cost Based Oracle: Fundamentals

> Hi, all
> I created a global temporary table, and many
> will populate huge
> volume of data into this table. All of these
> 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
> 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
> --
> --
> 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


Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
Received on Sun Sep 24 2006 - 05:36:23 CDT

Original text of this message