Re: create table as select * takes one commit?

Hi Ed,

even if you create a huge table with small RBS, it should be no problem, because the _before-image_ is copied to the RBS. A newly created row has only a very small before image (few bytes). So I believe you can create a 10 GB table with less than 100MB of rollback.

You can test it by creating a new table with a subset of data and keep tracing v$transaction.used_ublk, which shows you the space requirements of the "create table as select ...".

Regards
Stephan

"Ed Wong" <ewong_at_mail.com> wrote in message news:a5ae1554.0204202316.67d897b7_at_posting.google.com...
> 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
Received on Mon Apr 22 2002 - 02:15:39 CDT