Re: Create Index with nologging?

Peter

The quick answer is that no this will not affect your logging. Also except for the stuff I talk about below with regards to speeding it up there is nothing speical you need to do to turn loggin back on.

The long answer: The nologging option in the index creation statement applies to the creation of the index only. Depending on what you mean by logging, either archive/redo or transaction monitoring, the same level will continue to apply to the index and table associated with the index once you have created it. With regards to the backup, you are correct you need to backup after the table imports/index builds if you are using archive logging as otherwise you will have no point to roll forward from in event of a disaster.

As to speeding it all up a couple of ideas that I have found work.

If you are going to do an offline backup at the end of this operation and during the import/index build the users are not using the database.... turn off archiving, alter the temporary sort / retained size to be much larger, turn off any tracing you may have on (Most of this is a change to your init.ora and involves a restart of the dB, take a backup of the init.ora). Remember to put it all back to normal before letting the users in (by replacing your new init.ora with your backup and restarting the dB). If you are doing this and want to prevent users connecting either restrict your dB or if necessary shutdown the listener.

Split your index builds into different files for different groups of tables, as many files as you have CPU's (assuming only the one dB on this machine) and kick them off in parallel.

Regards

Fraser McCallum
MVP Oracle
www.brainbench.com

"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
Received on Tue Jul 24 2001 - 17:55:29 CDT