具有参考分区的直接路径插入? [英] Direct-path inserts with reference partitioning?

问题描述

如何使用直接路径插入通过引用分区将数据快速加载到表中?直接路径插入不适用于已启用的外键，并且无法禁用参考分区表的外键.

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.

这篇关于具有参考分区的直接路径插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！