Re: CTAS versus INSERT

On 27 May 2002 11:16:52 -0700, calberto2312_at_hotmail.com (Carlos Alberto) wrote:

>Hi,
>
> I want to load a large volume of data into a table. I was wondering
>what would be better : use CREATE TABLE AS SELECT, or create the table
>empty and then make INSERT. Both of options would use the NOLOGGING
>clause. I wonder CTAS would be faster, because in this case (almost)
>no log would be generated, instead of INSERT, which always generates
>log. Is it right?
>

You can go both ways to minimize (but not suppress totally) redo generation. Be aware that you have to backup your database afterwards.

(1)
CREATE TABLE t_new
NOLOGGING
PARALLEL
As
SELECT *
FROM t_old
/

(2)
CREATE TABLE t_new
As
SELECT *
FROM t_old
/

ALTER TABLE t_new NOLOGGING
/

INSERT /*+ APPEND PARALLEL */
INTO t_new
SELECT *
FROM t_old
/

ALTER TABLE t_new LOGGING
/

Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Mon May 27 2002 - 14:07:05 CDT