我无法使用PL / sql在Oracle表中执行大量插入操作.我的查询逐行进行,对于每一行,查询进行计算以确定需要插入另一个表的行数.传统的插入工作,但代码需要很长时间才能运行大量的行.为了加快插入速度,我尝试使用Append_Values提示,如下例所示：
BEGIN FOR iter in 1..100 LOOP INSERT /*+ APPEND_VALUES*/ INTO test_append_value_hint values (iter); END LOOP; END;
执行此操作时,我收到以下错误消息：
ORA-12838: cannot read/modify an object after modifying it in parallel
ORA-06512: at line 3
12838. 00000 - "cannot read/modify an object after modifying it in parallel"
*Cause: Within the same transaction,an attempt was made to add read or
modification statements on a table after it had been modified in parallel
or with direct load. This is not permitted.
*Action: Rewrite the transaction,or break it up into two transactions
one containing the initial modification and the second containing the
parallel modification operation.
解决方法
您收到此错误,因为您的每个INSERT都作为单独的DML语句执行. Oracle阻止对使用直接路径插入添加数据的表进行读/写,直到提交为止.
从技术上讲,您可以使用PL / sql集合和FORALL：
从技术上讲,您可以使用PL / sql集合和FORALL：
sql> declare 2 type array_t is table of number index by pls_integer; 3 a_t array_t; 4 begin 5 for i in 1..100 loop 6 a_t(i) := i; 7 end loop; 8 forall i in 1..100 9 insert /*+ append_values */ into t values (a_t(i)); 10 end; 11 /
但Justin问的问题是在行动 – 你的数据来自哪里,为什么你不能使用通常的INSERT / *追加* / INTO … SELECT FROM方法？
