append and parallel hints, nologging

in order to append into a table, you have to lock it - preventing all other sessions from touching it.
so, if you insert /*+ APPEND */ into table select .... - table is locked.



alter session enable parallel dml;

create table deneme ( x int );

insert /*+ parallel(deneme,8) */ into deneme select 0 from dual;

SELECT * FROM deneme

ORA-12838: cannot read/modify an object after modifying it in parallel




**********************************************************************************
create table deneme ( x int );
insert /*+ append */ into deneme select 0 from dual;
SELECT * FROM deneme
ORA-12838: cannot read/modify an object after modifying it in paralel




However, not all operations support NOLOGGING mode, the following is a list of operations that support NOLOGGING:

direct load (SQL*Loader)
direct-load
INSERT CREATE TABLE ... AS SELECT
CREATE INDEX
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


***********************************************************
parallel and append => direct path load
directly into datafile bypass buffer cache and above HWM
***********************************************************

direct path load + logging = no undo, but full redo
direct path load + nologging = no undo, almost no redo (exceptions are space transactions and block invalidation record)
conventional path load + loggging = full undo, full redo
conventional path load + nologging = full undo, full redo (i.e. nologging has no effect if direct path is not used)




alter table big_table parallel ;

Şeklinde yazıp parallelism derecesini Oracle ‘a bırakıyor ve Oracle kaynaklarını uygunsa dinamik olarak parallelik derecesini artıracaktır
ya da kaynaklar yogunsa paralellik derecesini düşürecektir


Nologging:

NOLOGGING is create table and alter table clause, you can't use it as part of insert statement.