INSERT statement with nologging
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: INSERT statement with nologging

Hybrid View

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    INSERT statement with nologging

    In the below insert statement, is there any advantage of NOLOGGING option? Does it prevent logging?

    INSERT /*+ APPEND */ INTO errorlogging NOLOGGING
    ( primarykey
    , source
    , result
    , timestamp
    , wherewasi
    , processed_count
    , process_id )
    SELECT
    p_primarykey
    , p_source
    , SUBSTR(p_result, 1, 1000)
    , SYSDATE
    , NULL
    , NULL
    , 1
    FROM dual;

  2. #2
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Yes of course ....its faster coz u wont generate redo logs as in
    direct path insert.

  3. #3
    Join Date
    Dec 2005
    Posts
    195
    Thanks for the response. But APPEND hint(Without NOLOGGING) itself bypass the redo log. What is the use of NOLOGGING here?

  4. #4
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    redundant i guess...just to make sure oracle wont forget it

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    There will of course be logging of index operations, regardless of the table logging.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Here's a complet Tom Kyte answer ... http://asktom.oracle.com/pls/ask/f?p...#4118658603057

    Data dictionary operations required by the insert will still generate minimal redo of course.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Dec 2005
    Posts
    195
    Dave, Yes. I agree. The index will be logging regardless of table logging. But if we set index to NOLOGGING, it should not log. Correct me if i am wrong.

    After reading few materials, here is my understanding.

    Direct load insert does not support for the below circumstances.

    1. If the table has Foreign key constraint
    2. If the table is clustered
    3. If the table has database trigger

    Thanks Dave. This site is very useful.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by pranavgovind
    Dave, Yes. I agree. The index will be logging regardless of table logging. But if we set index to NOLOGGING, it should not log. Correct me if i am wrong.
    Index creation and rebuild will notbe logged, but all DML that affects the index will generate index logging, even direct path insert.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Apr 2006
    Posts
    377
    The APPEND hint in and of itself will not prevent logging from occurring, regardless of the existence of an index. The table itself would have to be created as NOLOGGING or you'd have to be in NOARCHIVELOG mode.

  10. #10
    Join Date
    Sep 2006
    Posts
    6

    Alias?

    In this case, does this simply not just give the table an alias of NOLOGGING?

    Code:
    SQL> SET TRIMSPOOL ON
    SQL> SET LINES 129
    SQL> DROP TABLE ERRORLOGGING;
    DROP TABLE ERRORLOGGING
               *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    SQL> CREATE TABLE ERRORLOGGING
      2  (primarykey NUMBER
      3  , SOURCE VARCHAR2(30)
      4  , result VARCHAR2(300)
      5  , timestamp DATE
      6  , wherewasi VARCHAR2(30)
      7  , processed_count	NUMBER
      8  , process_id NUMBER
      9  );
    
    Table created.
    
    SQL> CREATE SEQUENCE p_primarykey;
    
    Sequence created.
    
    SQL> CREATE SEQUENCE p_source;
    
    Sequence created.
    
    SQL> CREATE SEQUENCE p_result;
    
    Sequence created.
    
    SQL> INSERT /*+ APPEND */ INTO errorlogging NOLOGGING
      2  ( primarykey
      3  , source
      4  , result
      5  , timestamp
      6  , wherewasi
      7  , processed_count
      8  , process_id )
      9  SELECT
     10  p_primarykey.NEXTVAL
     11  , p_source.NEXTVAL
     12  , SUBSTR(p_result.NEXTVAL, 1, 1000)
     13  , SYSDATE
     14  , NULL
     15  , NULL
     16  , 1
     17  FROM dual;
    
    1 row created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SELECT COUNT(1) FROM ERRORLOGGING;
    
      COUNT(1)
    ----------
             1
    

    Ok, the above works as expected. I just simply used sequences for the parameters.

    The following is going to fail... because errorlogging is now NOLOGGING.

    Code:
    SQL> INSERT /*+ APPEND */ INTO errorlogging NOLOGGING
      2  ( errorlogging.primarykey
      3  , errorlogging.source
      4  , errorlogging.result
      5  , errorlogging.timestamp
      6  , errorlogging.wherewasi
      7  , errorlogging.processed_count
      8  , errorlogging.process_id )
      9  SELECT
     10  p_primarykey.NEXTVAL
     11  , p_source.NEXTVAL
     12  , SUBSTR(p_result.NEXTVAL, 1, 1000)
     13  , SYSDATE
     14  , NULL
     15  , NULL
     16  , 1
     17  FROM dual;
    , errorlogging.process_id )
      *
    ERROR at line 8:
    ORA-00904: "ERRORLOGGING"."PROCESS_ID": invalid identifier
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SELECT COUNT(1) FROM ERRORLOGGING;
    
      COUNT(1)
    ----------
             1
    Now, this will work, because I am calling the proper alias of NOLOGGING.
    Code:
    SQL> INSERT /*+ APPEND */ INTO errorlogging NOLOGGING
      2  ( NOLOGGING.primarykey
      3  , NOLOGGING.source
      4  , NOLOGGING.result
      5  , NOLOGGING.timestamp
      6  , NOLOGGING.wherewasi
      7  , NOLOGGING.processed_count
      8  , NOLOGGING.process_id )
      9  SELECT
     10  p_primarykey.NEXTVAL
     11  , p_source.NEXTVAL
     12  , SUBSTR(p_result.NEXTVAL, 1, 1000)
     13  , SYSDATE
     14  , NULL
     15  , NULL
     16  , 1
     17  FROM dual;
    
    1 row created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SELECT COUNT(1) FROM ERRORLOGGING;
    
      COUNT(1)
    ----------
             2
    
    SQL> SPOOL OFF
    Or am I completely missing a post already saying this?

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