This content has been marked as final. Show 26 replies
1-) why nologging doesnt work here?There just isn't such an option. It's only a table alias the way you used it:
SQL> create table t (col1 number, col2 varchar2(30));
SQL> insert into t NOLOGGING
4 select object_id
6 from user_objects;
4 rows created.
1-) why nologging doesnt work here?1)
2-)Does parallel is by default direct path loading so
that there is no need to use append hint in this
Basically you are pointed out a syntax error here. NOLOGGING is create table and alter table clause, you can't use it as part of insert statement.
2) you already had your answer.
There are few things need to clarify,
1. No matter what, DML will generate redo, it's only matter of how much redo generated. All DML generate undo, undo in turn generate redo, also database dictionary change as result of DML will also generate redo. You can not turn off these redo generation in any way (except using an underscore parameter)
In addition to those, you have regular redo for the data changed.
2. Table need to be in NOLOGGING mode in order to achieve minimal redo generation as result of direct path loading, parallel query etc..
To put table in NOLOGGING, either include NOLOGGING clause at CREATE TABLE or ALTER TABLE with NOLOGGING clause
3. No all DML operations can make use of NOLOGGING, regular DML (update/insert/delete) will generate redo disregard NOLOGGING setting.
Following is a list of operation that can make use of nologging:
direct load (SQL*Loader)4. As I have mentioned parallel insert is by default direct path operation, so it can make use of nologging.
INSERT CREATE TABLE ... AS SELECT
ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... SPLIT PARTITION
ALTER INDEX ... SPLIT PARTITION
ALTER INDEX ... REBUILD
ALTER INDEX ... REBUILD PARTITION
INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line
Check Oracle doc,
The APPEND keyword is not required in this example because it is implied by the PARALLEL hint.
Check this article, it has good information about parallel processing,
3. No all DML operations can make use of NOLOGGING,regular DML (update/insert/delete) will generate redo disregard NOLOGGING setting.So even the table is in nologging mode,,regular DML (update/insert/delete) will still generate redo.Correct?
Using append hint writes directly to the database files I think no logging needed, because we bypass the buffer cache right?