Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: reducing logfile activity

    We're loading 30 million records into a table, and we've set the table to NOLOGGING along with all of it's indexes, but we're still getting archive logs being created at the same rate.

    I thought that NOLOGGING minimized this effect. Is there something else we can do? Following the load, we will be taking a backup of the table.

    -Chuck

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Code:
    Consider Using NOLOGGING When Creating Tables
    To create a table most efficiently use the NOLOGGING clause in the 
    CREATE TABLE...AS SELECT statement. The NOLOGGING clause 
    causes minimal redo information to be generated during 
    the table creation. This has the following benefits:
    
    Space is saved in the redo log files.
    
    The time it takes to create the table is decreased.
    
    Performance improves for parallel creation of large tables.
    
    The NOLOGGING clause also specifies that subsequent direct loads 
    using SQL*Loader and direct load INSERT operations are not logged. 
    Subsequent DML statements (UPDATE, DELETE, and conventional 
    path insert) are unaffected by the NOLOGGING attribute of the 
    table and generate redo.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    So setting up the INDEXES as NOLOGGING really doesn't matter, direct-path insert or not?

    -cf

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Indexes with NOLOGGING have reduced logging with direct path inserts I believe. Have you tried doing the inserts with the APPEND hint as this should help if your tables and indexes are nologging.

    Also if your doing lots of commits then this can also increase redo log activity.

    Alan

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Also, NOLOGGING is for bulk operations (direct path inserts, etc.), so depending on how you're loading data, it can or not generate the same redo. You should also check if your database has the FORCE_LOGGING option set to yes, if that is so, regardless of the objects being NOLOGGING it will always generate redo.

    I dont know, but I have done some test, and it seems that regardless the indexes being nologging/logging, redo is *always* generated.

Posting Permissions

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