Jan Korecki wrote:
>
>
>> It overrides nologging. I was under the impression that +append has to
>> do with extend management, not with logging. But I could be wrong here.
>>
>> So, during those inserts, ANY operations with nologging in it will not be
>> logged. Those may be a lot more operations than the inserts you want to
>> do.
>> As for trust, do you trust *anybody* not to make mistakes?
>>
>>
>>> But as i said i did not know about the "force logging" so i have to
>>> check it up first if it has any drawbacks.
>>
>>
>> http://otn.oracle.com/pls/db92/db92.drilldown?levelnum=1&preference=&expand_all=&book=Admin&method=FULL&chapters=0&verb=&word=force+logging#a96540
>>
>>
>>
>> Lots of Greetings!
>> Volker
>>
>
>
> +append writes over the high water mark (HWM)
>
> You have to specifically type +append or nologging in every sqlstatement
> to avoid logging.
>
> You cannot use +append on a normal insert: insert /*
> +append*/ into x values(.....)
>
> The hint is ignored in the above case. You cannot +append updates or
> deletes either.
>
>
>
> I have a few corrections to make. It was a while ago i used +append.
> If you are in archivelog mode you have to set the table to nologging
> when you use +append.
> If you are in noarchivelog mode you dont have to set the table to
> nologging.
>
> Se my examples below:
>
> -----------------
>
> create table test3 as select * from all_objects where 1=2;
>
>
> -- Archivelog off
>
> SQL> set autotrace STATISTICS
>
> SQL> alter table test3 logging;
>
> Table altered.
>
> SQL> insert into test3 select * from all_objects;
>
> 35576 rows created.
>
>
> Statistics
> ----------------------------------------------------------
> 4222652 redo size
>
>
>
> SQL> insert /*+append*/ into test3 select * from all_objects;
>
> 35576 rows created.
>
>
> Statistics
> ----------------------------------------------------------
> 864 redo size
>
> SQL> rollback;
>
> Rollback complete.
>
> SQL> alter table test3 nologging
> 2 /
>
> Table altered.
>
> SQL> insert /*+append*/ into test3 select * from all_objects;
>
> 35576 rows created.
>
>
> Statistics
> ----------------------------------------------------------
> 864 redo size
>
>
>
>
>
> -- archivelog on
>
> SQL> alter table test3 logging;
>
> Table altered.
>
>
> SQL> set autotrace on STATISTICS
> SQL> insert into test3 select * from all_objects;
>
> 35576 rows created.
>
>
>
> Statistics
> ----------------------------------------------------------
> 4148776 redo size
>
>
> SQL> rollback;
>
> Rollback complete.
>
>
> SQL> insert /*+append*/ into test3 select * from all_objects;
>
> 35576 rows created.
>
>
> Statistics
> ----------------------------------------------------------
> 4164056 redo size
>
>
> SQL> rollback;
>
> Rollback complete.
>
>
> SQL> alter table test3 nologging;
>
> Table altered.
>
> SQL> insert /*+append*/ into test3 select * from all_objects;
>
> 35576 rows created.
>
>
> Statistics
> ----------------------------------------------------------
> 864 redo size
>
>
>
> ----------------
>
> Sorry if i confused anyone.
>
> As for trust... i dont even trust myself most of the times :-)
>
>
>
> Regards,
> Janne!
>
>
>
> Disclaimer:
> The above are the opinions of God as recited by my telepathic goldfish.
> Those who oppose them with be struck by lightning. Such an event will
> be reflected on their electrical bill.
>
>
>
Huh? The +append hint has nothing to do with logging:
SQL> insert into test3 select * from all_objects;
25451 rows created.
Statistics
2897640 redo size
SQL> insert /* +append*/ into test3 select * from all_objects;
25451 rows created.
Statistics
2864320 redo size
SQL> /
25451 rows created.
Statistics
2867148 redo size
SQL> insert into test3 select * from all_objects;
25451 rows created.
Statistics
2869784 redo size
Beats me where the spread in redo comes from, but the
pattern is clear - hint or not - redo size is the same
--
Regards, Frank van Bortel
Received on Wed Nov 19 2003 - 15:00:56 CST
