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
- Oracle 9.2 Database Concepts Guide: about direct path inserts
- Oracle 9.2 SQL Reference-insert statement
- Oracle 12.1 SQL Reference-insert statement
Have fun!
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.
Thanks Bryan!
Your comment prompted me to look a little closer at the documentation and I found another useful reference. For those who like to read up on that, have a look at this document (it probably is the equivalent of chapter 19 from the 9.2 days):
https://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN01509
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:
Thanks,
Oren.
Hi Oren,
I think I should have made that clearer in the text. For the sake of completeness I added the trigger example:
Now it’s a conventional load.