Re: create table as select * takes one commit?

In article <a9v6io$mq4$1_at_lust.ihug.co.nz>, "Howard says...
>
>Marc Blum wrote:
>
>> 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:
....

>>
>> 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
>>
>
>
>Yeah, as ever I've over-stated the case. I was actually answering the
>question whether nologging switches off undo just as it switches off redo,
>and of course it doesn't, but to say it has no effect whatsoever is just
>silly, and my hyperbole gone mad.
>
>APPEND means chuck the stuff where no-one can read it (above the high water
>mark). If no-one can read it, we don't have read consistency issues. If we
>don't have read-consistency issues, we don't need the undo.
>
>So yes, it does have an effect.
>
>On the other hand, practically it isn't going to make that much difference,
>because of indexes. Assuming that most tables these days have an index on
>them somewhere, then undo still gets generated in quite substantial
>quantities because the index maintenance performed by the bulk load still
>has to generate undo -index entries have to take place where they are
>positionally meaningful - ie, where anyone can read them. So undo is needed
>for read-consistent images of the leaf nodes.
>
>However, you originally said that undo was 'minimized',so you were
>right and I was wrong, because even with stacks of indexes in place, you'll
>be generating much less undo than you would have done without the APPEND
>hint.
>
>My apologies.
>
>Regards
>HJR
>

but the index maintenance is done rather more efficiently with the append as well. it's done in bulk at the end and can result in a significant savings. Consider:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects;

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create index t_idx1 on t(owner,object_name,object_type);

Index created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create index t_idx2 on t(object_id);

Index created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t select * from all_objects;

22880 rows created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;

 USED_UBLK

       361

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> rollback;

Rollback complete.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert /*+ APPEND */ into t select * from all_objects;

22880 rows created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;

 USED_UBLK

       212

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> rollback;

Rollback complete.

The insert /*+ append */ in this case generated 59% the amount of UNDO as the regular insert.

I'd say that practically -- this does make a sizable difference.

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