Re: Create Index with nologging?

Pete Finnigan wrote:
>
> 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

Just to add...

If an indexed table is defined as nologging, then insert-append will have reduced redo log for the table change but will log the index changes in the normal fashion...

hth

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."