Jan Korecki wrote:
>
>
> 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.
>
>
>
You're right! Absolutely!
--
Regards, Frank van Bortel
Received on Thu Nov 20 2003 - 14:59:50 CST
