Printable View
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;
Yes of course ....its faster coz u wont generate redo logs as in
direct path insert. :)
Thanks for the response. But APPEND hint(Without NOLOGGING) itself bypass the redo log. What is the use of NOLOGGING here?
redundant i guess...just to make sure oracle wont forget it :D
There will of course be logging of index operations, regardless of the table logging.
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.
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.
Index creation and rebuild will notbe logged, but all DML that affects the index will generate index logging, even direct path insert.Quote:
Originally Posted by pranavgovind
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.
In this case, does this simply not just give the table an alias of NOLOGGING? :confused:
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.
Now, this will work, because I am calling the proper alias of 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
Or am I completely missing a post already saying this?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
Precisely herodt, the NOLOGGING in the SELECT as you proved is basically an alias. The table you are inserting into needs to be defined as NOLOGGING when in ARCHIVELOG mode in order to reap the benefits of reduced logging.
David, Okay. So we can not bypass the index log for all DML that affects the index, even direct path insert. I understand now.
Quote:
Originally Posted by slimdave