Re: create table as select * takes one commit?

On Mon, 22 Apr 2002 06:13:21 +1000, "Howard J. Rogers" <dba_at_hjrdba.com> wrote:

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

Thank you for the very instructional answer. Now I have a better picture of the things going on behind the scenes.

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Mon Apr 22 2002 - 00:49:31 CDT