Re: Q: emulating SQLPLUS COPY

From: Galen Boyer <galendboyer_at_yahoo.com>
Date: 8 Apr 2002 22:20:11 -0500
Message-ID: <ud6x9jsp7.fsf@rcn.com>

On 8 Apr 2002, igoriz_at_cmtk.net wrote:
> Thomas Kyte <tkyte_at_oracle.com> wrote in message

>> 1) the append hint on the insert will not do undo, it writes above
>> the hwm of the table directly.  If the table is 'nologging', it won't
>> even generate redo
>> 

>
> SQL> create table t (f1 varchar2(100)) nologging;
>
> Table created.
>
> SQL> insert /*+ append */ into t select sysdate from dual;
>
> 1 row created.
>
> SQL> rollback;
>
> Rollback complete.
>
> SQL> select * from t;
>
> no rows selected
>
> SQL>
>
> Doesn't it mean that undo IS generated ?

My guess is that the previous HWM is written to an undo and the rollback resets the HWM to that before you did the append. So, if this is the case, I guess undo is generated, but not in the sense of all rows inserted are generating undo, just directly when the operation starts a single piece of undo is generated.

    SQL> insert into t1 select * from merchandise_hierarchy_tbl;

    5026091 rows created.

    Elapsed: 00:09:582.99
    SQL> rollback;

    Rollback complete.

    Elapsed: 00:20:1208.07

    SQL> insert /*+ append */ into t1 select * from merchandise_hierarchy_tbl;

    5026091 rows created.

    Elapsed: 00:10:624.68
    SQL> rollback;

    Rollback complete.

    Elapsed: 00:00:00.80

Seems to be the case.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

Received on Mon Apr 08 2002 - 22:20:11 CDT