Re: Why does INSERT ... AS SELECT ... use TEMP, contrary to CREATE TABLE ... AS SELECT...?

Home -> Community -> Usenet -> c.d.o.server -> Re: Why does INSERT ... AS SELECT ... use TEMP, contrary to CREATE TABLE ... AS SELECT...?

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Sat, 13 Jan 2007 10:33:04 GMT
Message-ID: <45a8b4c9.5701453@news.hetnet.nl>

On Sat, 13 Jan 2007 10:02:47 GMT, j.w.vandijk.removethis_at_hetnet.nl (Jaap W. van Dijk) wrote:

>Oracle 9.2.0.5.0 on Open VMS Itanium.
>
>I have a table SRC with degree default, and parallel dml enabled for
>the session. If I do:
>
>CREATE TABLE tgt
>PARALLEL NOLOGGING
>AS SELECT * FROM src
>
>data from SRC is read, and written immediately to TGT, in 4-fold
>parallel.
>
>If I then truncate table TGT, and do a
>
>INSERT INTO tgt
>SELECT * FROM src
>
>data from SRC is read into temporary tablespace buffers first, and
>after that the buffer contents is written to TGT, again all in 4-fold
>parallel.
>
>Why the detour in case of the INSERT?
>
>Regards, Jaap.

I got the change to try this out for Oracle 10g on Windows XP. In that case the INSERT writes directly to the target table, so it seems to be an Oracle version or OS issue.

Jaap. Received on Sat Jan 13 2007 - 04:33:04 CST