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 --
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


Other related posts: