INSERT statement with nologging

[pranavgovind]

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;

[yxez]

Yes of course ....its faster coz u wont generate redo logs as in
direct path insert.

[pranavgovind]

Thanks for the response. But APPEND hint(Without NOLOGGING) itself bypass the redo log. What is the use of NOLOGGING here?

[yxez]

redundant i guess...just to make sure oracle wont forget it

[slimdave]

There will of course be logging of index operations, regardless of the table logging.

[slimdave]

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.

[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.

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.

[slimdave]

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.

[ebrian]

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.

[herodt]

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

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.

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.

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?