Nologging, index and direct-load insert - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Nologging, index and direct-load insert

  1. #11
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Originally posted by ales
    ALTER INDEX NOLOGGING really sets the NOLOGGING column in TABS to "NO".
    Sorry, of course not, it sets the column in IND.

    I'm saying that:
    In case of direct-load INSERT, a table with nologging and its indexes with nologging generates significantly more redo than a table without indexes with nologging.

    In other words:
    In case of direct-load INSERT, indexes on the populated table generate significant amount of redo regardless of their NOLOGGING status.

  2. #12
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    In the SQL LOADER control file, set UNRECOVERABLE if you do not want redo/undo info while loading bulk data in DIRECT mode.

    NOLOGGING at table level or Index level will not help LOADER to generate redo/undo info.

  3. #13
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Originally posted by tamilselvan
    In the SQL LOADER control file, set UNRECOVERABLE if you do not want redo/undo info while loading bulk data in DIRECT mode.
    Unfortunately, I do not use SQL LOADER but direct-load insert (INSERT /*+ APPEND */ INTO ... SELECT ....)

    The behavior I described is reported as "normal" in Usenet forum.

    The right method should be:
    1. drop indexes of the table
    2. set the table NOLOGGING
    3. populate the table
    4. recreate the indexes NOLOGGING
    5. set the table and their indexes LOGGING
    6. backup the database

    I'm going to direct-load six tables this sunday and I post some report about the result.

    Thanks to tamilselvan and thanks to pando who helped me very much to ask the right question :-)).

    Ales


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