Martins Blog

Trying to explain complex things in simple terms

Little things worth knowing: direct path inserts and referential integrity

Posted by Martin Bach on April 13, 2015

This is another post to remind myself that Oracle evolves, and what I thought I knew might no longer be relevant. So double-checking instead of assuming should become a habit!

Today’s example: direct path inserts. I seemed to remember from Oracle 9i that a direct path insert ignores referential integrity. This is still confirmed in the 9i Release 2 Concepts Guide, chapter 19 “Direct Path Insert”. Quoting from there:

During direct-path INSERT operations, Oracle appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored

That sounds a bit harsh in today’s times so it’s worth a test. On Oracle 12.1.0.2 I created a parent/child relationship, admittedly rather crude:

SQL> create table parent (id, vc) as
  2   select distinct data_object_id, subobject_name from dba_objects
  3   where data_object_id is not null;

Table created.

SQL> alter table parent add constraint pk_parent primary key (id);

Table altered.

SQL> create table child (id number, p_id number not null, vc varchar2(100),
  2  constraint pk_child primary key (id),
  3  constraint fk_parent_child foreign key (p_id) references parent (id));

Table created.

Now when I try to insert data using a direct path insert it fails:

SQL> insert /*+ append */ into child select s_child.nextval, -1, 'this will not work' from parent;
insert /*+ append */ into child select s_child.nextval, -1, 'this will not work' from parent
*
ERROR at line 1:
ORA-02291: integrity constraint (MARTIN.FK_PARENT_CHILD) violated - parent key not found

Which is brilliant and what I expected since it prevents me from writing a lot of garbage into my table. If you have developed with Oracle then you probably know about deferrable constraints. An existing constraint can’t be changed to a status of “initially deferrable”, which is why I have to drop it and then try the insert again:

SQL> alter table child drop constraint FK_PARENT_CHILD;

Table altered.

Elapsed: 00:00:00.04
SQL> alter table child add constraint FK_PARENT_CHILD foreign key (p_id) references parent (id)
  2  deferrable initially deferred;

Table altered.

Elapsed: 00:00:00.02
SQL> insert /*+ append */ into child select s_child.nextval, -1, 'this will not work' from parent;

7640 rows created.

Elapsed: 00:00:00.30
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (MARTIN.FK_PARENT_CHILD) violated - parent key not found

So this contraint fires as well! Good news for me. If it’s all ok then the insert will of course succeed.

Trouble is that Oracle “silently” ignores the direct path load! I haven’t initially put this into the post but thanks to Oren for adding it to it in the comments section (make sure to have a look at it).

Back to the “hot” constraint definition and inserting into the table yields the expected result.

SQL> alter table child drop constraint FK_PARENT_CHILD;

Table altered.

SQL> alter table child add constraint FK_PARENT_CHILD foreign key (p_id) references parent (id);

Table altered.

SQL> insert /*+ append */ into child select s_child.nextval, 2, 'this should work however' from parent;

7640 rows created.

SQL> commit;

Commit complete.

Summary

Again-the insert wasn’t really a “direct path insert”, see comments. It really pays off to verify and update “old knowledge” from time to time! I still prefer valid data over garbage, and when it comes to ILM I can “move” my table to get the required compression result.

References

Have fun!

4 Responses to “Little things worth knowing: direct path inserts and referential integrity”

  1. Great post.. I also wanted to point out that with direct path inserts, the index updates/referential integrity checks happen after the insertion of the data (all at once). Typically this is done on a row-by-row basis taking more time This makes large Direct path inserts more efficient. I think the biggest drawback, is that you need to commit the data before selecting from the table you just inserted into (within the same session). Yes, Oracle is constantly improving and evolving.

  2. Hi Martin.
    The enabled foreign key (either immediate or deferrable) causes the INSERT to work in a conventional mode. The APPEND hint is simply ignored, and it’s not a direct-path insert. The same happens if there are triggers on the table.
    We can see it in the EXPLAIN PLAN (the operation is LOAD TABLE CONVENTIONAL), and in 12c it is even kind enough to also add an explicit note:

    
    ------------------------------------------------------------------------------
    | Id  | Operation                | Name      | Rows  | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT         |           |  8630 |     7   (0)| 00:00:01 |
    |   1 |  LOAD TABLE CONVENTIONAL | CHILD     |       |            |          |
    |   2 |   SEQUENCE               | S_CHILD   |       |            |          |
    |   3 |    INDEX FAST FULL SCAN  | PK_PARENT |  8630 |     7   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    
    Note
    -----
       - Direct Load disabled because parent referential constraints are present
    
    and when the constraint is deferrable, the note is:
    
    Note
    -----
       - Direct Load disabled because deferrable constraints are used
    
    

    Thanks,
    Oren.

    • Martin Bach said

      Hi Oren,

      I think I should have made that clearer in the text. For the sake of completeness I added the trigger example:

      SQL> create trigger t_child after insert on child
        2  begin
        3   null;
        4  end;
        5  /
      
      Trigger created.
      
      SQL> insert /*+ append */ into child select s_child.nextval, -1, 'test' from parent;
      
      7640 rows created.
      
      Elapsed: 00:00:00.20
      SQL> select * from table(dbms_xplan.display_cursor);
      
      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------
      SQL_ID  f0c75km7w14wq, child number 0
      -------------------------------------
      insert /*+ append */ into child select s_child.nextval, -1, 'test' from
      parent
      
      Plan hash value: 2561479089
      
      ------------------------------------------------------------------------------------
      | Id  | Operation                      | Name      | Rows  | Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------------
      |   0 | INSERT STATEMENT               |           |       |     6 (100)|          |
      |   1 |  LOAD TABLE CONVENTIONAL       | CHILD     |       |            |          |
      |   2 |   SEQUENCE                     | S_CHILD   |       |            |          |
      |   3 |    INDEX STORAGE FAST FULL SCAN| PK_PARENT |  7640 |     6   (0)| 00:00:01 |
      ------------------------------------------------------------------------------------
      
      Note
      -----
         - Direct Load disabled because triggers are defined
      
      

      Now it’s a conventional load.

      
      SQL> alter trigger t_child disable;
      
      Trigger altered.
      
      SQL> insert /*+ append */ into child select s_child.nextval, -1, 'test' from parent;
      
      7640 rows created.
      
      SQL> select * from table(dbms_xplan.display_cursor);
      
      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------
      SQL_ID  f0c75km7w14wq, child number 0
      -------------------------------------
      insert /*+ append */ into child select s_child.nextval, -1, 'test' from
      parent
      
      Plan hash value: 787139841
      
      --------------------------------------------------------------------------------------
      | Id  | Operation                        | Name      | Rows  | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------
      |   0 | INSERT STATEMENT                 |           |       |     6 (100)|          |
      |   1 |  LOAD AS SELECT                  |           |       |            |          |
      |   2 |   OPTIMIZER STATISTICS GATHERING |           |  7640 |     6   (0)| 00:00:01 |
      |   3 |    SEQUENCE                      | S_CHILD   |       |            |          |
      |   4 |     INDEX STORAGE FAST FULL SCAN | PK_PARENT |  7640 |     6   (0)| 00:00:01 |
      --------------------------------------------------------------------------------------
      
      

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: