APPEND

The APPEND hint lets you enable direct-path INSERT if your database is running in serial mode. 
Your database is in serial mode if you are not using Enterprise Edition. 
Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode.

In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. 
As a result, direct-path INSERT can be considerably faster than conventional INSERT.

/*+ APPEND */

/*+ APPEND */
/*+ NOAPPEND*/

«Подсказка APPEND форсирует использование оптимизатором direct-path INSERT в запросах вида INSERT INTO… SELECT …

        Традиционный (conventional) метод вставки в таблицу используется по умолчанию (без хинтов) в режиме 
непараллельной вставки. В этом режиме direct path INSERT может быть использован только при использовании подсказки APPEND.
        Direct-path INSERT используется по умолчанию в режиме параллельной вставки [работает при выполнении след.условий:

                Oracle Enterprise Edition

                ALTER SESSION { ENABLE | FORCE } PARALLEL DML;

                установленный признак параллельности (USER_TABLES.DEGREE) для целевой таблицы на этапе 
создания/модификации (CREATE|ALTER TABLE…PARALLEL n)

            или подсказкой PARALLEL во время выполнения DML

            или параметером PARALLEL_DEGREE_POLICY = AUTO (11.2+)]

    В этом случае традиционный (conventional) метод вставки строк в таблицу будет применяться только при использовании подсказки NOAPPEND.

… При использовании direct-path INSERT, данные добавляются в конец таблицы [добавляя новые блоки и повышая High Water Mark (HWM)], 
вместо того, чтобы использовать свободное место в уже выделенных блоках таблицы. В результате direct-path INSERT может быть 
значительно быстрее традиционной (conventional) операции вставки»

Особенности использования подсказки APPEND в запросах вида INSERT INTO … VALUES, см. APPEND Hint (Direct-Path) Insert 
with Values Causes Excessive Space Usage on 11G [ID 842374.1]:

1. В версиях 10g, 10g R2, подсказка APPEND игнорируется

2. В 11g R1, хинт APPEND запускает режим direct-path insert даже в случае использования INSERT INTO … VALUES. 
Поведение аналогично использованию подсказки APPEND_VALUES в версиях, начиная с 11g R2.

3. Начиная с версии 11g R2, появляется новая подсказка APPEND_VALUES, позволяя т.о использовать механизм direct path load 
только в случае использования APPEND_VALUES. Подсказка APPEND игнорируется для предложений типа INSERT … VALUES…

Рекомендуется использовать хинт APPEND (режим прямой вставки) для загрузки больших объёмов данных, а не для вставки 
одиночных строк, т.к. в последнем случае не будет получено преимуществ
...

select version from v$instance;
VERSION    
 ---------- 
 10.2.0.4.0 

create table t1(
id number,
value varchar2(256));
insert into t1 (id,value) select rownum, 'sdfghsjdfhgjhsdfhg34kjhkjHKJhjролр ЛОРОРОЛРОЛРОР jkhhjhjhHJHJHJHJHKJH jhjhjh 338458376873 kjkjkjkjk 090sdf9gng45n' from dual connect by rownum<=2048;

DBMS_OUTPUT:
 ------------
 
 2048 record(s) affected 

 [Executed: 8/6/2017 6:41:13 PM] [Execution: 21ms] 

select extent_id, bytes, blocks
 from user_extents
 where segment_name = 'T1'
 and segment_type = 'TABLE';

DBMS_OUTPUT:
 ------------

 EXTENT_ID     BYTES     BLOCKS    
 ------------  --------  --------- 
 0             65536     8         
 1             65536     8         
 2             65536     8         
 3             65536     8         
 4             65536     8         

 5 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms] 

 [Executed: 8/6/2017 6:42:08 PM] [Execution: 21ms] 

delete from t1 where id<=1024;

1024 record(s) affected 
 [Executed: 8/6/2017 6:44:15 PM] [Execution: 40ms] 

select extent_id, bytes, blocks
 from user_extents
 where segment_name = 'T1'
 and segment_type = 'TABLE';

 DBMS_OUTPUT:
 ------------
 EXTENT_ID     BYTES     BLOCKS    
 ------------  --------  --------- 
 0             65536     8         
 1             65536     8         
 2             65536     8         
 3             65536     8         
 4             65536     8         

 5 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms] 

 [Executed: 8/6/2017 6:45:59 PM] [Execution: 20ms] 
insert into t1 (id,value) select rownum, 'sdfghsjdfhgjhsdfhg34kjhkjHKJhjролр ЛОРОРОЛРОЛРОР jkhhjhjhHJHJHJHJHKJH jhjhjh 338458376873 kjkjkjkjk 090sdf9gng45n' from dual connect by rownum<=1024;

DBMS_OUTPUT:
 ------------
 1024 record(s) affected 

 [Executed: 8/6/2017 6:46:52 PM] [Execution: 162ms] 
select extent_id, bytes, blocks
 from user_extents
 where segment_name = 'T1'
 and segment_type = 'TABLE' ;
DBMS_OUTPUT:
 ------------
  EXTENT_ID     BYTES     BLOCKS    
 ------------  --------  --------- 
 0             65536     8         
 1             65536     8         
 2             65536     8         
 3             65536     8         
 4             65536     8         

 5 record(s) selected [Fetch MetaData: 1ms] [Fetch Data: 0ms] 

 [Executed: 8/6/2017 6:47:52 PM] [Execution: 22ms] 

delete from t1 where id<=1024;
DBMS_OUTPUT:
 ------------
 
1024 record(s) affected 

 [Executed: 8/6/2017 6:49:42 PM] [Execution: 105ms] 
select extent_id, bytes, blocks
 from user_extents
 where segment_name = 'T1'
 and segment_type = 'TABLE' ;
 DBMS_OUTPUT:
 ------------
 

 EXTENT_ID     BYTES     BLOCKS    
 ------------  --------  --------- 
 0             65536     8         
 1             65536     8         
 2             65536     8         
 3             65536     8         
 4             65536     8         

 5 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 1ms] 

 [Executed: 8/6/2017 6:50:27 PM] [Execution: 26ms] 
insert /*+ APPEND*/ into t1 (id,value) select rownum, 'sdfghsjdfhgjhsdfhg34kjhkjHKJhjролр ЛОРОРОЛРОЛРОР jkhhjhjhHJHJHJHJHKJH jhjhjh 338458376873 kjkjkjkjk 090sdf9gng45n' from dual connect by rownum<=1024;
DBMS_OUTPUT:
 ------------

 1024 record(s) affected 

 [Executed: 8/6/2017 6:51:08 PM] [Execution: 50ms] 

select extent_id, bytes, blocks
 from user_extents
 where segment_name = 'T1'
 and segment_type = 'TABLE' ;
DBMS_OUTPUT:
 ------------
 
 EXTENT_ID     BYTES     BLOCKS    
 ------------  --------  --------- 
 0             65536     8         
 1             65536     8         
 2             65536     8         
 3             65536     8         
 4             65536     8         
 5             65536     8         
 6             65536     8         

 7 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 1ms] 

 [Executed: 8/6/2017 6:51:53 PM] [Execution: 21ms]

delete from t1;
select extent_id, bytes, blocks
 from user_extents
 where segment_name = 'T1'
 and segment_type = 'TABLE' ;

DBMS_OUTPUT:
 ------------
  EXTENT_ID     BYTES     BLOCKS    
 ------------  --------  --------- 
 0             65536     8         
 1             65536     8         
 2             65536     8         
 3             65536     8         
 4             65536     8         
 5             65536     8         
 6             65536     8         

 7 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 1ms] 

 [Executed: 8/6/2017 6:51:53 PM] [Execution: 21ms]

insert /*+ APPEND*/ into t1 (id,value) select rownum, 'sdfghsjdfhgjhsdfhg34kjhkjHKJhjролр ЛОРОРОЛРОЛРОР jkhhjhjhHJHJHJHJHKJH jhjhjh 338458376873 kjkjkjkjk 090sdf9gng45n' from dual connect by rownum<=1024;

DBMS_OUTPUT:
 ------------

 1024 record(s) affected 

 [Executed: 8/6/2017 7:12:46 PM] [Execution: 61ms] 
select extent_id, bytes, blocks
 from user_extents
 where segment_name = 'T1'
 and segment_type = 'TABLE' ;
DBMS_OUTPUT:
 ------------
  EXTENT_ID     BYTES     BLOCKS    
 ------------  --------  --------- 
 0             65536     8         
 1             65536     8         
 2             65536     8         
 3             65536     8         
 4             65536     8         
 5             65536     8         
 6             65536     8         
 7             65536     8         
 8             65536     8         
 9             65536     8         

 10 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 1ms] 

 [Executed: 8/6/2017 7:13:47 PM] [Execution: 21ms] 

SQL> drop table t1;
create table t1(
id number,
value varchar2(256));
Table dropped.

SQL>   2    3  

Table created.

SQL> 
SQL> insert into t1 (id,value) select rownum, 'sdfghsjdfhgjhsdfhg34kjhkjHKJhjролр ЛОРОРОЛРОЛРОР jkhhjhjhHJHJHJHJHKJH jhjhjh 338458376873 kjkjkjkjk 090sdf9gng45n' from dual connect by rownum<=1024;
insert into t1 (id,value) select rownum, 'sdfghsjdfhgjhsdfhg34kjhkjHKJhjролр ЛОРОРОЛРОЛРОР jkhhjhjhHJHJHJHJHKJH jhjhjh 338458376873 kjkjkjkjk 090sdf9gng45n' from dual connect by rownum<=1024;
commit;
select extent_id, bytes, blocks
 from user_extents
 where segment_name = 'T1'
 and segment_type = 'TABLE' ;
1024 rows created.

SQL> 
1024 rows created.

SQL> 
Commit complete.

SQL>   2    3    4  

 EXTENT_ID	BYTES	  BLOCKS
---------- ---------- ----------
	 0	65536	       8
	 1	65536	       8
	 2	65536	       8
	 3	65536	       8
	 4	65536	       8

SQL> delete from t1;
commit;
select extent_id, bytes, blocks
 from user_extents
 where segment_name = 'T1'
 and segment_type = 'TABLE' ;
2048 rows deleted.

SQL> 
Commit complete.

SQL>   2    3    4  

 EXTENT_ID	BYTES	  BLOCKS
---------- ---------- ----------
	 0	65536	       8
	 1	65536	       8
	 2	65536	       8
	 3	65536	       8
	 4	65536	       8

SQL> insert into t1 (id,value) select rownum, 'sdfghsjdfhgjhsdfhg34kjhkjHKJhjролр ЛОРОРОЛРОЛРОР jkhhjhjhHJHJHJHJHKJH jhjhjh 338458376873 kjkjkjkjk 090sdf9gng45n' from dual connect by rownum<=1024;
insert into t1 (id,value) select rownum, 'sdfghsjdfhgjhsdfhg34kjhkjHKJhjролр ЛОРОРОЛРОЛРОР jkhhjhjhHJHJHJHJHKJH jhjhjh 338458376873 kjkjkjkjk 090sdf9gng45n' from dual connect by rownum<=1024;
commit;
select extent_id, bytes, blocks
 from user_extents
 where segment_name = 'T1'
 and segment_type = 'TABLE' ;
1024 rows created.

SQL> 
1024 rows created.

SQL> 
Commit complete.

SQL>   2    3    4  

 EXTENT_ID	BYTES	  BLOCKS
---------- ---------- ----------
	 0	65536	       8
	 1	65536	       8
	 2	65536	       8
	 3	65536	       8
	 4	65536	       8

SQL> delete from t1;
commit;
insert /*+ APPEND*/ into t1 (id,value) select rownum, 'sdfghsjdfhgjhsdfhg34kjhkjHKJhjролр ЛОРОРОЛРОЛРОР jkhhjhjhHJHJHJHJHKJH jhjhjh 338458376873 kjkjkjkjk 090sdf9gng45n' from dual connect by rownum<=1024;
commit;
select extent_id, bytes, blocks
 from user_extents
 where segment_name = 'T1'
 and segment_type = 'TABLE' ;
2048 rows deleted.

SQL> 
Commit complete.

SQL> 
1024 rows created.

SQL> 
Commit complete.

SQL>   2    3    4  

 EXTENT_ID	BYTES	  BLOCKS
---------- ---------- ----------
	 0	65536	       8
	 1	65536	       8
	 2	65536	       8
	 3	65536	       8
	 4	65536	       8
	 5	65536	       8
	 6	65536	       8

7 rows selected.

SQL> delete from t1;
commit;
insert into t1 (id,value) select rownum, 'sdfghsjdfhgjhsdfhg34kjhkjHKJhjролр ЛОРОРОЛРОЛРОР jkhhjhjhHJHJHJHJHKJH jhjhjh 338458376873 kjkjkjkjk 090sdf9gng45n' from dual connect by rownum<=1024;
insert into t1 (id,value) select rownum, 'sdfghsjdfhgjhsdfhg34kjhkjHKJhjролр ЛОРОРОЛРОЛРОР jkhhjhjhHJHJHJHJHKJH jhjhjh 338458376873 kjkjkjkjk 090sdf9gng45n' from dual connect by rownum<=1024;
commit;
select extent_id, bytes, blocks
 from user_extents
 where segment_name = 'T1'
 and segment_type = 'TABLE' ;

1024 rows deleted.

SQL> 
Commit complete.

SQL> 
1024 rows created.

SQL> 
1024 rows created.

SQL> 
Commit complete.

SQL>   2    3    4  
 EXTENT_ID	BYTES	  BLOCKS
---------- ---------- ----------
	 0	65536	       8
	 1	65536	       8
	 2	65536	       8
	 3	65536	       8
	 4	65536	       8
	 5	65536	       8
	 6	65536	       8

7 rows selected.

SQL>