NOLOGGING on indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: NOLOGGING on indexes

  1. #1
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    Question

    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?

  2. #2
    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)
    Ramon Caballero, DBA, rcaballe@yahoo.com

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    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.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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]
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    Question

    What is direct-load insert?

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

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes, exactly.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    Wink

    Thank you gentlemen for your time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width