Re: create table as select * takes one commit?

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Mon, 22 Apr 2002 06:13:21 +1000
Message-ID: <a9v6io$mq4$1@lust.ihug.co.nz>

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

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   Received on Sun Apr 21 2002 - 15:13:21 CDT