This can be replaced by a PL/SQL selecting into an array, doing bulk
inserts and committing regularly. PL/SQL will not perform as well as the
SQL below but will probably perform good enough. There is a rather
infamous optimization of commit in a PL/SQL loop which will mitigate the
impact of a gazillion commits. As for the conjecture about indexes, it
is most likely correct. Direct insert uses the blocks after the HWM
which are empty so there is not much content in the data blocks to
write to the undo space. The content is probably coming from the index
blocks.
On 3/21/21 1:16 AM, Pap wrote:
--
INSERT /*+ append parallel(16) nologging */ INTO USER1.TAB (c1, c2, c3, c4... c11) SELECT...;
