2017/09/18

APPEND hint placement

It is pretty surprising,  that APPEND hint can be placed not only after INSERT keyword but also after SELECT keyword of a subquery.

Check these execution plans:

SQL> create table tab1 as select * from dba_objects;

Table TAB1 created.

SQL> create table tab2 as select * from tab1 where 1=2;

Table TAB2 created.

SQL> explain plan for
  2* insert /*+ append */ into tab2 select * from tab1;
Explained.

SQL> @geteplan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
Plan hash value: 888952673

----------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |      | 98784 |   350   (1)|
|   1 |  LOAD AS SELECT                  | TAB2 |       |            |
|   2 |   OPTIMIZER STATISTICS GATHERING |      | 98784 |   350   (1)|
|   3 |    TABLE ACCESS FULL             | TAB1 | 98784 |   350   (1)|
----------------------------------------------------------------------

SQL> explain plan for
  2* insert into tab2 select * from tab1;
Explained.

SQL> @geteplan

PLAN_TABLE_OUTPUT
--------------------------------------------------------------
Plan hash value: 2211052296

--------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Cost (%CPU)|
--------------------------------------------------------------
|   0 | INSERT STATEMENT         |      | 98784 |   350   (1)|
|   1 |  LOAD TABLE CONVENTIONAL | TAB2 |       |            |
|   2 |   TABLE ACCESS FULL      | TAB1 | 98784 |   350   (1)|
--------------------------------------------------------------

SQL> explain plan for
  2* insert into tab2 select /*+ append */ * from tab1;
Explained.

SQL> @geteplan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
Plan hash value: 888952673

----------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |      | 98784 |   350   (1)|
|   1 |  LOAD AS SELECT                  | TAB2 |       |            |
|   2 |   OPTIMIZER STATISTICS GATHERING |      | 98784 |   350   (1)|
|   3 |    TABLE ACCESS FULL             | TAB1 | 98784 |   350   (1)|
----------------------------------------------------------------------

And this fact is documented in a quite unusual place. See 20.4.2.3.1 Serial Mode Inserts with SQL Statements of Admin Guide for more details.