Thursday, January 28, 2016

INSERT /*+ APPEND */ Hint Does Not Seem to Work Consistently

I ran the following SQL in an Oracle 11.2.0.1 database:

SQL> create table tab1
  2  (col1 number)
  3  /
 
Table created.
 
SQL> alter session set sql_trace = true
  2  /
 
Session altered.
 
SQL> insert /*+ append */ into tab1 select 1 from dual
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> insert /*+ append */ into tab1 values(2)
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         1
         2
 
SQL> alter session set sql_trace = false
  2  /
 
Session altered.
 
SQL>

Then I ran the trace file through tkprof. For some reason, the first SQL used a Direct Path load:

SQL ID: f57gyxg0uqcgb
Plan Hash: 1432430773
insert /*+ append */ into tab1 select 1 from dual
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.02       0.02          0          3         30           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.02       0.02          0          3         30           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8354  (ORACLE)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD AS SELECT  (cr=0 pr=0 pw=0 time=0 us)
      1   FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   LOAD AS SELECT OF 'TAB1'
      1        FAST DUAL

 
... but the second one didn’t. The strange reformatting of the insert statement took place in tkprof, not in Blogger:

SQL ID: 6pb8960hm1hpy
Plan Hash: 0
insert /*+ append */ into tab1
values
(2)
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          7         25           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          7         25           1
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8354  (ORACLE)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=7 pr=0 pw=0 time=0 us)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   LOAD TABLE CONVENTIONAL OF 'TAB1'


P.S. Shortly after publishing this post, a number of people corrected it in the comments below. I won't be changing the post; if I did, the comments would then be out of place. However, I will be testing and understanding the comments before using them as the basis for another post in the near future.

4 comments:

Alistair Wall said...

Append only works with insert/select. To use direct path inserts with a values clause, you have to use insert_values: http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#BABFIHGA

Dominic Brooks said...

APPEND doesn't work with VALUES.
You'd have to use the newer (11gR2) APPEND_VALUES hint.

Anonymous said...

Try the 11gR2 new APPEND_VALUES hint. https://oracle-base.com/articles/misc/append-hint

Andrew Reid said...

Thank you all for your comments.
I will try out your suggestions and write a new post in a day or two.