Re: create table as select * takes one commit?

On 21 Apr 2002 00:16:38 -0700, ewong_at_mail.com (Ed Wong) wrote:

>I am looking into ways to copy a 10GB table. A easy way is to do:
>create tableb as select * from tablea;
>or
>create tableb as select /*+ APPEND +/ * from tablea;
>
>Is it the same as DML that goes into rollback segment? I don't have
>10GB of rbs and I don't want to increase my rbs just to do this.
>
>Also, if I want to use the APPEND hint, do I need to say "insert /*+
>APPEND */..." or I can simply do "create table /*+ APPEND */ ? What's
>the difference?
>
>Thanks in advance.
>
>ewong

What you need is

(a)

CREATE TABLE t_b

   NOLOGGING
AS
SELECT * FROM t_a
/

or
(b)

CREATE TABLE t_b
(...column list...)
/

ALTER TABLE t_b NOLOGGING
/

INSERT /*+ APPEND */
INTO t_b
SELECT * FROM t_a
/

ALTER TABLE t_b LOGGING
/

That way redo and undo generation is minimized.

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sun Apr 21 2002 - 04:21:44 CDT