什么会导致 Oracle 忽略请求它执行直接路径加载的 APPEND 提示?

我希望将批量数据从一个表插入到另一个表中进行串行记录.这是一次数据迁移的一部分，因此交换分区等不是答案.

SQL 将具有以下结构:

INSERT/*+ APPEND */... SELECT FROM ....

什么可能导致 Oracle 运行此常规插入而不是直接路径插入?

例如，我相信在表上有一个触发器会导致 Oracle 进行常规插入.是否有明确的限制清单?

解决方案

引用自 http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1211797200346279484 :p>

"insert/*+ append */将忽略附加提示并在表具有引用完整性或触发器时使用常规路径加载..." - Tom Kyte

来自 https://docs.oracle.com/cd/E18283_01/server.112/e17118/statements_9014.htm#sthref6486

<块引用>

直接路径 INSERT 受到许多限制.如果有任何一个违反了这些限制，然后 Oracle 数据库执行常规 INSERT 串行而不返回任何消息，除非另有说明:

• 您可以在单个事务中拥有多个直接路径 INSERT 语句，无论是否有其他 DML 语句.然而，一DML 语句更改特定的表、分区或索引，没有其他事务中的 DML 语句可以访问该表、分区或索引.

• 在直接路径 INSERT 语句之前允许访问同一个表、分区或索引的查询，但不允许在它之后.

• 如果任何串行或并行语句尝试访问已被直接路径 INSERT 修改的表事务，然后数据库返回错误并拒绝声明.

• 目标表不能是集群的.

• 目标表不能包含对象类型列.

• 如果索引组织表 (IOT) 未分区、具有映射表或未分区，则不支持直接路径 INSERT通过物化视图引用.

• 直接路径插入到索引组织表 (IOT) 的单个分区中，或插入到只有一个分区的分区 IOT 中，将串行完成，即使 IOT 是在并行模式下创建的，或者您指定 APPEND 或 APPEND_VALUES 提示.但是，直接路径 INSERT对分区 IOT 的操作将遵循并行模式，只要未使用分区扩展名且 IOT 有多个分区.

• 目标表上不能定义任何触发器或参照完整性约束.

• 无法复制目标表.

• 包含直接路径 INSERT 语句的事务不能或变为分布式.

但是，手册中的列表并不详尽，也不完全准确.例如，如果该约束是引用分区的一部分，则引用完整性约束不会阻止直接路径 INSERT.

I wish to do a serial, logging insert of bulk data from one table to another. This is once-of as part of a data migration so swapping partitions, etc is not an answer.

The SQL will be of the following structure:

INSERT /*+ APPEND */ ... SELECT FROM ....

What might cause Oracle to run this a convential insert rather than a direct path insert?

For example, I believe having a trigger on the table will cause Oracle to conduct a convential insert. Is there a definitive list of restrictions?

解决方案

A quote from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1211797200346279484 :

"insert /*+ append */ will ignore the append hint and use conventional path loading when the table has referential integrity or a trigger..." - Tom Kyte

From https://docs.oracle.com/cd/E18283_01/server.112/e17118/statements_9014.htm#sthref6486

Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:

• You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.

• Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.

• If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the database returns an error and rejects the statement.

• The target table cannot be of a cluster.

• The target table cannot contain object type columns.

• Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view.

• Direct-path INSERT into a single partition of an index-organized table (IOT), or into a partitioned IOT with only one partition, will be done serially, even if the IOT was created in parallel mode or you specify the APPEND or APPEND_VALUES hint. However, direct-path INSERT operations into a partitioned IOT will honor parallel mode as long as the partition-extended name is not used and the IOT has more than one partition.

• The target table cannot have any triggers or referential integrity constraints defined on it.

• The target table cannot be replicated.

• A transaction containing a direct-path INSERT statement cannot be or become distributed.

However the list in the manual is not exhaustive and is not completely accurate. For example, referential integrity constraints do not block direct-path INSERT if that constraint is part of reference partitioning.