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

    Unanswered: Avoiding logging in INSERT

    I am setting up some tables to hold a backup copy of some data. What is the syntax for inserting data from that backup copy back into the original table which avoids REDO?

    I'll basically just be doing the following:

    Code:
    create table myTable_bck as select * from myTable;
    
    exec myPackage;
    
    truncate table myTable;
    
    insert /*+ NOLOGGING*/ into myTable
    select * from myTable_bck;
    I found NOLOGGING, but I think that only applies to CREATE TABLE and ALTER TABLE statements. I found documentation on Direct-Path inserts using the /*+ APPEND */ hint, and the documentation described using a logging vs a non-logging approach, but didn't clearly mention how to manage that part.

    Thanks,
    Chuck

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Don't confuse the NOLOGGING property of tables and indexes with the APPEND hint. There is no NOLOGGING hint.

    CREATE TABLE mybackuptable NOLOGGING AS SELECT * FROM othertable;

    defines the new table as having the NOLOGGING property. You could also

    ALTER TABLE sometable NOLOGGING;

    btw if the table is only bulk-loaded and never updated or altered you might also try PCTFREE 0 and COMPRESS to make more efficient use of space.

    You are then ready to perform a direct path INSERT, with

    INSERT /*+ APPEND */ INTO mybackuptable SELECT * FROM othertable;

    The hint will have no effect unless the table is defined as NOLOGGING (or unless the database is running in noarchivelog mode, in which case everything is effectively NOLOGGING anyway).

    Now spot the difference:

    Code:
    SQL> set autotrace on stat        
    SQL> INSERT INTO t_nologging SELECT * FROM all_objects;
    
    4200 rows created.
    
    Statistics
    ----------------------------------------------------------
            114  recursive calls
            616  db block gets
          50898  consistent gets
              0  physical reads
         451000  redo size
            621  bytes sent via SQL*Net to client
            545  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
           4200  rows processed
    
    SQL> TRUNCATE TABLE t_nologging;
    
    Table truncated.
    
    SQL> INSERT /*+ APPEND */ INTO t_nologging SELECT * FROM all_objects;
    
    4200 rows created.
    
    Statistics
    ----------------------------------------------------------
            117  recursive calls
             68  db block gets
          50804  consistent gets
              0  physical reads
           7600  redo size
            606  bytes sent via SQL*Net to client
            559  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
           4200  rows processed
    Note there will always be some logging as the database must log its extent management and so on.
    Last edited by WilliamR; 01-07-06 at 15:05.

Posting Permissions

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