Re: create table as select * takes one commit?

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Sun, 21 Apr 2002 22:33:51 +1000
Message-ID: <a9ubl8$898$1@lust.ihug.co.nz>

Marc Blum wrote:

> 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

I'll buy the redo generation being minimised. But care to explain how the undo generation is minimised?

It isn't, is the short answer. 'Nologging' does what it says: switches off the generation of redo in the log buffer (and hence in the redo logs). It has no effect whatsoever on undo (rollback) generation.

Regards
HJR Received on Sun Apr 21 2002 - 07:33:51 CDT