Re: Cloning a table

Method one (create table as select or CTAS) is one of the few commands you can carry out that will respect a nologging attribute. The comand should therefore be

create table clone
nologging
as select * from original;

...and this will go much faster than your second method because no redo is being generated, it is thus not inducing logswitches and attendant checkpoints/archiving activity.

Your second method is a conventional insert, with all the locking, redo and rollback issues associated with inserts (and no possibility of suppressing redo generation). However, you can speed that up considerably by making it a direct-load insert with the APPEND hint:

insert /*+APPEND*/ into clone select * from original;

That performs block writes above the high water mark, which is inherently faster than conventional inserts.

As far as I can recall off the top of my head, neither of your methods has any advantages over the other when it comes to rollback generation. Both generate rollback (which is relatively small, this being inserts we're doing). It's the *redo* issue that's different.

Regards
HJR

--
Resources for Oracle: http://www.hjrdba.com
===============================


"Silver" <boltsilver_at_hotmail.com> wrote in message
news:ad3752b9.0111281928.32d22b97_at_posting.google.com...


> This is possibly a very basic question, but I am not able to figure it

> out. Imagine a large table TAB1(col1 number, col2 varchar2(50)) with

> few million rows. Compare the following two methods.

>

> 1) create table TAB1_CLONE (

>    col1,

>    col2,

>    constraint PK_TAB1_CLONE primary key(col1)

>    )

>    as

>    select col1, col2 from tab1;

>

> 2) create table TAB1_CLONE

>    col1 number,

>    col2 varchar2(50),

>    constraint PK_TAB1_CLONE primary key(col1)

>    );

>

>    insert into TAB1_CLONE (col1, col2) select col1, col2 from TAB1;

>    commit;

>

> Now my understanding is that if TAB1 is very large, using method 2

> might give some sort of "rollback segment too small" or "transaction

> log full" kind of error, since we commit only after all rows have been

> inserted. On the other hand, method 1 does not seem to have any such

> errors, and seems faster. How does this work ?

>

> Thanks.

>

> - Silver