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