Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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