如何使用直接路径插入通过引用分区将数据快速加载到表中？直接路径插入不适用于启用的外键,并且无法禁用引用分区表的外键.
- 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
无需禁用外键.直接路径插入使用引用分区,即使
the documentation暗示它不应该.
下面的LOAD AS SELECT操作演示了使用直接路径插入：
- 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 | |
- --------------------------------------------------------
如果直接路径插入不能与引用分区一起使用,那是因为许多其他限制之一,例如触发器,不同的外键,可延迟约束等.
