Direct-path inserts with reference partitioning?

Question

How can I use direct-path inserts to quickly load data into a table with reference partitioning? Direct-path inserts do not work with enabled foreign keys and the foreign key of a reference-partitioned table cannot be disabled.

SQL> create table parent_table(a number primary key)
  2  partition by range(a) (partition p1 values less than (1));

Table created.

SQL> create table child_table(a number not null
  2     ,constraint child_table_fk foreign key (a) references parent_table(a))
  3  partition by reference (child_table_fk);

Table created.

SQL> alter table child_table disable constraint child_table_fk;
alter table child_table disable constraint child_table_fk
*
ERROR at line 1:
ORA-14650: operation not supported for reference-partitioned tables

Answer 1

There is no need to disable the foreign key. Direct-path inserts work with reference partitioning even though the documentation implies it should not.

The LOAD AS SELECT operation below demonstrates that direct-path inserts are used:

explain plan for insert /*+ append */ into child_table select 1 from dual;

select * from table(dbms_xplan.display(format => 'basic'));

Plan hash value: 2781518217

--------------------------------------------------------
| Id  | Operation                        | Name        |
--------------------------------------------------------
|   0 | INSERT STATEMENT                 |             |
|   1 |  LOAD AS SELECT                  | CHILD_TABLE |
|   2 |   OPTIMIZER STATISTICS GATHERING |             |
|   3 |    FAST DUAL                     |             |
--------------------------------------------------------

If direct-path inserts do not work with reference-partitioning it is because of one of the many other restrictions, such as triggers, a different foreign key, deferrable constraints, etc.