RE: problem about full scan temporary table
- From: qihua wu <wqhhp@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Sun, 24 Sep 2006 03:36:23 -0700 (PDT)
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Sunday, September 24, 2006 3:31 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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
> --
> //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
>
>
--
//www.freelists.org/webpage/oracle-l
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
