Re: Create Index with nologging?

Hi

If you read the book on oracle tuning for 8i and unix he shows that /*+ append */ doesnt actually mean no-logging. What /*+ append */ means is that new extents are used to store the data on the tablespace. What this does for you is avoid searching the free lists. Its a small waste of disk space but will give you a speed advantage if the table you are inserting into has a lot of deletes done on it. If no records are ever deleted then there should be no entries in the free list anyway so append wont make much difference. The free list as i understand it is a list of free blocks within the block its writing to and its a linked list held in the block header.

cheers

Pete Finnigan
www.pentest-limited.com

In article <3B6AA397.828FC403_at_oracle.com>, Ghassan Salem <Ghassan.Salem_at_oracle.com> writes
>hi,
>you need to add the /*+ append*/ hint to get rid of logging, otherwise
>this is not coinsidered as 'bulk insert', and hence it is logged.
>rgds
>
>Steve McDaniels wrote:
>
>> updates? you're correct.
>>
>> however, if I do
>>
>> create table ....
>>
>> create index ... no logging
>>
>> THEN DO an update, the update gets logged.
>>
>> If I then do
>>
>> insert into table
>> select * from...
>>
>> the index changes are NOT logged.
>>
>> updates and insert into table values (...) get logged.
>> bulk inserts (via select from) don't.
>>
>> "Guruju,Narendra" <NGURUJU_at_cerner.com> wrote in message
>> news:D15ED542E12BD3119FFE00805F6551F00BE369D9_at_mailwhqnews.cerner.com...
>> > Steve,
>> > alter index nologging will work only during the create. It has no affect
>> > once the index is created. So even with nologging option turned on
>> > inserts, updates to the index would still be logged on.
>> >
>> > -----Original Message-----
>> > From: Steve McDaniels [mailto:steve.mcdaniels_at_sierra.com]
>> > Posted At: Tuesday, July 24, 2001 5:50 PM
>> > Posted To: comp.databases.oracle.server
>> > Conversation: Create Index with nologging?
>> > Subject: Re: Create Index with nologging?
>> >
>> >
>> > You know you can alter index logging (or nologging) as often as you
>> > like.
>> > It only affects operations _after_ the alter statement.
>> >
>> > import your data.
>> > create index nologging
>> > [don't forget parallel (degree N)]
>> >
>> > when indexing complete,
>> > alter index logging
>> >
>> >
>> > "Peter Stryjewski" <pstryjew_at_worldnet.att.net> wrote in message
>> > news:3B5DED3B.F4F5365D_at_worldnet.att.net...
>> > > Hello,
>> > >
>> > > I have searched the Deja (now Google Groups) archives and the Ask Tom
>> > > page (how often does Tom take questions?) and found conflicting
 answers.
>> > >
>> > > Scenario, Oracle 8.1.x:
>> > >
>> > > Export DB from original system (with direct option for speed)
>> > > Move export file to new system
>> > > Use "indexfile" to get table create and index create statements
>> > > Create tables
>> > > Import Data without index build
>> > > ...
>> > > Now create indexes. This I would like to speed up!
>> > >
>> > > If I modify the index create statements, created by the "indexfile"
 and
>> > > add nologging (or is it norecovery?). This should speed things up.
>> > >
>> > > But what happens after the Create Index? I know I should take a
>> > > backup. But will all subsequent operations on the index be logged?
 Do
>> > > I have to ALTER the index, for this to happen? This is the portion
 with
>> > > a lot of conflicting information floating around.
>> > >
>> > > Does anyone have any good information on achieving the fastest import
>> > > and index build.
>> > >
>> > > Pete
>> >
>> >
>
>[ A MIME text / x-vcard part was included here. ]
>
 

-- 
Pete Finnigan