Frank wrote:
> 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
You have a spacing in /* +append*/
insert /* +append*/ into test3 select * from all_objects;
When i ran your sql in sqlplus it ignored the hint.
Type this instead:
insert /*+append*/ into test3 select * from all_objects;
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.
Received on Wed Nov 19 2003 - 15:51:45 CST
