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

插入/* + append */将忽略附加提示，并在表具有参照完整性或触发器时使用常规路径加载..."-Tom Kyte来自 https://docs.oracle. com/cd/E18283_01/server.112/e17118/statements_9014.htm#sthref6486 直接路径INSERT受到许多限制.如果有 违反这些限制，然后执行Oracle数据库 常规地串行插入而不返回任何消息，除非 另有说明: 您可以在单个事务中具有多个直接路径INSERT语句，可以使用或不使用其他DML语句.但是，经过一 DML语句会更改特定的表，分区或索引，没有其他更改 事务中的DML语句可以访问该表，分区或 索引. 在直接路径INSERT语句之前(但在其之后)不允许访问同一表，分区或索引的查询. 如果任何串行或并行语句尝试访问在同一目录中已经由直接路径INSERT修改过的表 事务，则数据库返回错误并拒绝 声明. 目标表不能属于集群. 目标表不能包含对象类型列. 如果索引分区表(IOT)未分区，具有映射表或包含索引表，则不支持 直接路径INSERT用于索引组织表(IOT) 通过实例化视图进行引用. 将直接路径插入到索引组织表(IOT)的单个分区中，或仅插入一个分区的分区IOT中， 即使IOT是在并行模式下创建的，也可以通过串行方式完成 指定APPEND或APPEND_VALUES提示.但是，直接路径INSERT 只要进入并行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 KyteFrom 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. 这篇关于是什么导致Oracle忽略要求APPEND提示执行直接路径加载的APPEND提示?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！