Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tony .

Asked: May 05, 2026 - 4:26 pm UTC

Last updated: May 08, 2026 - 7:05 am UTC

Version: 19c

Viewed 100+ times

You Asked

what is the difference between logging and nologging when creating an index

and Connor said...

"logging" is about storing redo entries for database recovery in case of emergency. Here's a scenario which we can look at from both logging and nologging perpsective

9am: create index MY_INDEX on MY_TABLE
10am: disk corruption, database needs restoring from backup

Logging case:
- while the index was being built at 9am, redo log information was captured for all index blocks created
- at 10am, we restore the database from (say) last nights backup
- we then roll forward our archived redo, which will include the logged index build, and our index comes back

NoLogging case:
- while the index was being built at 9am, NO redo log information was captured for all index blocks created
- at 10am, we restore the database from (say) last nights backup
- we then roll forward our archived redo, which is NO evidence of the index we created. The dictionary entry for the index will be there but the index itself will be basically be "absent"

You would then need to drop and create the index from scratch again

If you *know* you are planning nologging operations, then you could this:

9am: create index MY_INDEX on MY_TABLE nologging
***9:10am: Once the index is built, take a backup of the tablespace/datafiles that hold that index to ensure we have a copy of the index
10am: disk corruption, database needs restoring from backup

This limits your risk to a smaller window of time.

My general mode of operation is - I'll use logging (just to save myself from any hassles) until such point as build runtime performance becomes so critical as to need nologging (and then I'll look at appropriate backup measures to limit exposure)

Rating

  (1 rating)

Comments

Thank you !!

Tony Fernandez, May 06, 2026 - 1:07 pm UTC

Connor,

Thank you for this information. I was thinking redo logs only store ongoing DMLs and DDLs, but never though or never knew it also stores index data blocks. I will take your answer into account when doing indexes and thanks for the great job you guys do at the good old asktom website. Also, I watch all your office hours videos as they are quite informative and go beyond any documentation detail available.

Big thanks and kindest regards,
Connor McDonald
May 08, 2026 - 7:05 am UTC

Glad we could be of help

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.