RE: Nosort parallel dml uses TEMP tablespace

I'm even more confused now.

TEMP ran out of space at 20GB - I'm only inserting 12.2GB!

So I kicked it off again with the hint just as /*+ APPEND */ and it completed successfully in 1/6th the time to failure of the parallel insert.

> -----Original Message-----
> From: Turner, Adrian A SITI-ITPSIE
> Sent: 16 September 2003 15:16
> To: Multiple recipients of list ORACLE-L
> Subject: Nosort parallel dml uses TEMP tablespace
>
> Apologies if this has been covered before but metalink is not clear on the reasons behind it....
>
> The database is running version 9204 EE on WinNT Sp6; and the statement is a parallel direct load into partitioned table selecting from a 12.2GB source table.
>
> ALTER SESSION ENABLE PARALLEL DML ;
>
> INSERT /*+ APPEND PARALLEL(TRANSACTION_NEW,4) */ INTO TRANSACTION_NEW
> (select * from TRANSACTIONS);
>
> I'm seeing segments created in the temp tablespace (from v$sort_usage)
>
> Sess# User Name TABLESPACE CONTENTS ext BLOCKS SZ_MB
> ----- --------- ------------------------------- --------- -------- ---------- ----------
> 9 SYS TEMP2 TEMPORARY 1629 208512 1629
> 11 SYS TEMP2 TEMPORARY 1629 208512 1629
> 12 SYS TEMP2 TEMPORARY 1629 208512 1629
> 13 SYS TEMP2 TEMPORARY 1629 208512 1629
>
> I've enough space and it'll finish by tomorrow morning which is the deadline but does anyone know why TEMP is required and what the end to end process is?
>
> I would have expected to have seen temporary segments created, but in the partitions own tablespace. It doesnt seem optimal to me.
>
> Thanks in advance for your help,
>
> Regards,
> Adrian
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Turner, Adrian A SITI-ITPSIE
  INET: Adrian.Turner_at_shell.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).