NOLOGGING on indexes

[khussain]

If we create an index with the NOLOGGING option, what is the impact on recover in the case of a crash:

THIS IS WHAT I KNOW ABOUT IT:
With NOLOGGING on the index, when inserts are done, the data is written into the redo, but not the index. So if the database crashes, and requires the redo logs, we would get the data, but not the index. So after the recover we would have to go and rebuild the index. Is this a correct interpretation of the way Oracle handles NOLOGGING on indexes?

[rcaballe]

Yes.
After recovery you will have to recreate the indexes.
So you will have to have the scripts near you :)
(I have them that way, Datawarehouse)

[jmodic]

No (unless it was changed very lately).

NOLOGGING option means only that during index *creation* redo information is not written to redo logs. This means much quicker creation of the index, but once the index is all changes to the index (caused by inserts, updates or deletes) are recorded in redo logs as usually.

So only if your database crashes after you create index with NOLOGGING and before you've performed a backup you'll have to recreate that index. After you've performed a backup you are safe regarding that index (unless you perform some other nologged action, like direct load, in the future).

[khussain]

I got this from the Oracle 8.1.5 Doc:
LOGGING |
NOLOGGING
specifies that the creation of the index will be logged (LOGGING) or not logged
(NOLOGGING) in the redo log file. It also specifies that subsequent Direct Loader
(SQL*Loader) and direct-load INSERT operations against the index are logged or not logged.
LOGGING is the default.

So it seems like NOLOGGING does play a role in the case of (SQL*Loader) and direct-load INSERT operations.

[marist89]

I agree with jmodic. Operations which can be done via NOLOGGING will not be logged. However, all other operations will be logged.
See:
[url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/state10c.htm#2062976[/url]

[jmodic]

Yes, like I mentioned it at the end of my post. But for ordinar inserts (wasn't that what you have been asking about?) redo logs are still written to by that index.

[khussain]

What is direct-load insert?

Is it when the /+APPEND+/ hint is used during insert?

[jmodic]

Yes, exactly.

[khussain]

Thank you gentlemen for your time.