Oracle DBA – A lifelong learning experience

Restrictions on the append hint

Posted by John Hallas on November 22, 2015

I wanted to add an append hint to some code but realised that there are known restrictions when either triggers or referential integrity are involved.

That does make sense as potentially rows would be inserted that either did not fire a trigger or broke RI constraints.

I decided to produce a test case to prove that the restrictions did work and were still in place.

Test append with trigger created

set lines 240
set pages 0
create table test1 as select * from dba_objects;

create table test2 as select * from dba_objects;
truncate table test2;
insert into test2 select * from test1 where object_id > 47
select * from table(dbms_xplan.display_cursor);
----------------------------------------------------------------------------------
 | Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------------
 |   0 | INSERT STATEMENT         |       | 19848 |  4012K|    70   (0)| 00:00:01 |
 |   1 |  LOAD TABLE CONVENTIONAL | TEST2 |       |       |            |          |
 |*  2 |   TABLE ACCESS FULL      | TEST1 | 19848 |  4012K|    70   (0)| 00:00:01 |
 ----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
 ---------------------------------------------------
2 - filter("OBJECT_ID">47)
Note
 -----
 - dynamic sampling used for this statement (level=2)

Now lets use the append hint and see what things look like

 insert /*+ APPEND */ into test2 select * from test1 where object_id>47;

select * from table(dbms_xplan.display_cursor);
SQL_ID  1v2h4b58sgmk0, child number 1
 -------------------------------------
 insert /*+ APPEND */ into test2 select * from test1 where object_id > 47
Plan hash value: 1930563303
----------------------------------------------------------------------------
 | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------
 |   0 | INSERT STATEMENT   |       |       |       |   337 (100)|          |
 |   1 |  LOAD AS SELECT    |       |       |       |            |          |
 |*  2 |   TABLE ACCESS FULL| TEST1 | 86302 |    17M|   337   (1)| 00:00:05 |
 ----------------------------------------------------------------------------
Predicate Information (identified by operation id):
 ---------------------------------------------------
   2 - filter("OBJECT_ID">47)
Note
 -----
 - dynamic sampling used for this statement (level=2)

Note that the operation with an APPEND is classed as LOAD AS SELECT  – Direct Insert – rather than a normal insert which is LOAD TABLE CONVENTIONAL

OK, so far so good. If, as in some of my tests I did  you use PARALLEL as well as APPEND you might see the error  – ORA-12838

insert /*+ APPEND parallel(test2,4) */ into test2 select /*+ parallel(test1,4)  */ * from test1;

*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

This is expected if you have not committed after the first Direct Insert operation
After a Direct Load Insert, the session that performed the Insert may perform another Direct Load Insert on the same table,
but may not perform any other action (SELECT, UPDATE, DELETE, or conventional INSERT)
on that table until the transaction is committed.

I am now going to create a FK constraint and see if the hint still works

create table test3 as select distinct object_type from test1;

alter table test2 add constraint fk_test2_type foreign key object_type references test3(object_type);

create index test3_ind1 on test3(object_type);
alter table test3 add constraint object_type_pk primary key (object_type);
alter table test2 add constraint fk_test2_type foreign key (object_type) references test3(object_type);

insert /*+ APPEND */ into test2 select * from test1 where object_id > 47
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID  1v2h4b58sgmk0, child number 0
-------------------------------------
insert /*+ APPEND */ into test2 select * from test1 where object_id > 47

Plan hash value: 4122059633

----------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       |       |       |   337 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL |       |       |       |            |          |
|*  2 |   TABLE ACCESS FULL      | TEST1 | 86302 |    17M|   337   (1)| 00:00:05 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID">47)

Note
-----
   - dynamic sampling used for this statement (level=2)

As can be seen by the LOAD TABLE CONVENTIONAL line in the plan the APPEND hint has been ignored – with no warning

Now I will drop the constraints and add a trigger

alter table test3 drop primary_key object_type_pk ;
alter table test3 drop index test3_ind1;
alter table test2 drop constraint  fk_test2_type ;
alter table test3 drop constraint object_type_pk;

Prove that direct load is used

----------------------------------------------------------------------------
 | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------
 |   0 | INSERT STATEMENT   |       |       |       |   337 (100)|          |
 |   1 |  LOAD AS SELECT    |       |       |       |            |          |
 |*  2 |   TABLE ACCESS FULL| TEST1 | 86302 |    17M|   337   (1)| 00:00:05 |
 ----------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER ins_type_tr
 AFTER INSERT on test2
 FOR EACH ROW
 BEGIN
 insert into test3(object_type)
 values
 (:new.object_type);
 end ins_type_tr;
 /
SQL> insert /*+ APPEND parallel(test2,4) */ into test2 select * from test1 where object_id > 47;
 select * from table(dbms_xplan.display_cursor);
----------------------------------------------------------------------------------
 | Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------------
 |   0 | INSERT STATEMENT         |       |       |       |   346 (100)|          |
 |   1 |  LOAD TABLE CONVENTIONAL |       |       |       |            |          |
 |*  2 |   TABLE ACCESS FULL      | TEST1 |   104K|    20M|   346   (1)| 00:00:05 |
 ----------------------------------------------------------------------------------

Again a CONVENTIONAL LOAD is used do the hint has been ignored.

No great surprises here but sometimes I find it useful just to double-check things  and I do like to see demos, both in my blogs  and others as it always helps others to find an example to work with.

One Response to “Restrictions on the append hint”

  1. harish besant said

    Nice blog, Thanks for sharing the useful information with us. Keep update your blog frequently.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: