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?
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).
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
I got this from the Oracle 8.1.5 Doc:
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.
I agree with jmodic. Operations which can be done via NOLOGGING will not be logged. However, all other operations will be logged.