Re: create table as select * takes one commit?

On Sun, 21 Apr 2002 22:33:51 +1000, "Howard J. Rogers" <dba_at_hjrdba.com> wrote:

>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.
>

ups..sorry for that one

thank you for the correction.

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