问题描述
如何使用直接路径插入通过引用分区将数据快速加载到表中?直接路径插入不适用于已启用的外键，并且无法禁用参考分区表的外键.
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
推荐答案
无需禁用外键.即使文档暗示不应该.
There is no need to disable the foreign key. Direct-path inserts work with reference partitioning even though the documentation implies it should not.
下面的LOAD AS SELECT操作演示了使用直接路径插入:
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.
这篇关于具有参考分区的直接路径插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！