Re: create table as select * takes one commit?

In article <3cc2c58e.17127457_at_news.online.de>, marc_at_marcblum.de says...
>
>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.
>>
>
>but what about that one? Tom Kyte proves, that INSERT /*+ APPEND */
>generates minimized undo:
>
>http://groups.google.de/groups?hl=de&selm=a3k2kp08q5%40drn.newsguy.com&rnum=6
>

this example shows it more "quantatively" (with numbers, instead of just blowing out RBS as that example does)

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( x number );

Table created.

Elapsed: 00:00:00.28
ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t select rownum from sys.source$;

58343 rows created.

Elapsed: 00:00:03.79
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;

 USED_UBLK

        19

Elapsed: 00:00:00.01
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> rollback;

Rollback complete.

Elapsed: 00:00:00.33
ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert /*+ APPEND */ into t select rownum from sys.source$;

58343 rows created.

Elapsed: 00:00:02.81
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;

 USED_UBLK

         1

Elapsed: 00:00:00.01
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> rollback;

Rollback complete.

Elapsed: 00:00:00.03
ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

The append, since it writes above the HWM, doesn't need to generate UNDO for the inserted data. So, 19 blocks of "delete rowid" commands for the insert without the append. Not so for the insert with the append hint.

Now, if the table is indexed, the indexes will always generate undo for the insert statement (less but still there) as the index maintenance must be performed in the structure, not above the HWM for the index....

>regards
>Marc Blum
>mailto:marc_at_marcblum.de
>http://www.marcblum.de

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp