Oracle hint: APPEND

Influence on conventional load vs direct path insert

Preparation

Create two tables to test the influence of the append hint:

create table tq84_src (id number, val varchar2(100));
create table tq84_dest(id number, val varchar2(100));

Serial execution

Explain the plan for an insert into … select from statement …

explain plan for
   insert into tq84_dest
   select * from tq84_src;

… and display the plan:

select * from table(dbms_xplan.display(format=>'basic'));
--
-- ----------------------------------------------
-- | Id  | Operation                | Name      |
-- ----------------------------------------------
-- |   0 | INSERT STATEMENT         |           |
-- |   1 |  LOAD TABLE CONVENTIONAL | TQ84_DEST |
-- |   2 |   TABLE ACCESS FULL      | TQ84_SRC  |
-- ----------------------------------------------

Same thing, but this time using the append hint:

explain plan for
   insert into tq84_dest
   select /*+ append */ * from tq84_src;

select * from table(dbms_xplan.display(format=>'basic'));
--
-- ------------------------------------------------------
-- | Id  | Operation                        | Name      |
-- ------------------------------------------------------
-- |   0 | INSERT STATEMENT                 |           |
-- |   1 |  LOAD AS SELECT                  | TQ84_DEST |
-- |   2 |   OPTIMIZER STATISTICS GATHERING |           |
-- |   3 |    TABLE ACCESS FULL             | TQ84_SRC  |
-- ------------------------------------------------------

Parallel execution

explain plan for
   insert into tq84_dest
   select /*+ parallel(4) enable_parallel_dml */ * from tq84_src;
select * from table(dbms_xplan.display(format => 'basic +note'));
--
-- --------------------------------------------------------
-- | Id  | Operation                          | Name      |
-- --------------------------------------------------------
-- |   0 | INSERT STATEMENT                   |           |
-- |   1 |  PX COORDINATOR                    |           |
-- |   2 |   PX SEND QC (RANDOM)              | :TQ10000  |
-- |   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| TQ84_DEST |
-- |   4 |     OPTIMIZER STATISTICS GATHERING |           |
-- |   5 |      PX BLOCK ITERATOR             |           |
-- |   6 |       TABLE ACCESS FULL            | TQ84_SRC  |
-- --------------------------------------------------------
--  
-- Note
-- -----
--    - dynamic statistics used: dynamic sampling (level=2)
--    - Degree of Parallelism is 4 because of hint

Same thing, but using the noappend hint. This hint disables direct path («direct load», see Note of dbms_xplan.display below):

explain plan for
   insert into tq84_dest
   select /*+ parallel(4) noappend enable_parallel_dml */ * from tq84_src;
select * from table(dbms_xplan.display(format => 'basic +note'));
--
-- ------------------------------------------------
-- | Id  | Operation                  | Name      |
-- ------------------------------------------------
-- |   0 | INSERT STATEMENT           |           |
-- |   1 |  PX COORDINATOR            |           |
-- |   2 |   PX SEND QC (RANDOM)      | :TQ10000  |
-- |   3 |    LOAD TABLE CONVENTIONAL | TQ84_DEST |
-- |   4 |     PX BLOCK ITERATOR      |           |
-- |   5 |      TABLE ACCESS FULL     | TQ84_SRC  |
-- ------------------------------------------------
--  
-- Note
-- -----
--    - dynamic statistics used: dynamic sampling (level=2)
--    - Degree of Parallelism is 4 because of hint
--    - Direct Load disabled because noappend hint used

Cleaning up:

drop   table tq84_dest;
drop   table tq84_src ;