Re: Memory usage of Bulk Inserts

Yash wrote:
>
> Hi,
>
> In the application I am developing, I need to perform a bulk insert into a
> table by selecting from another table. I anticipate millions of rows in the
> source table.
>
> My question is, will the bulk insert statement try to gather all rows from
> the source in memory and then perform the insert?
>
> I want to make sure the operation does not fail due to lack of memory or
> lack of rollback segment space.
>
> Is there an option, or hint that I can give in the INSERT command to avoid
> running out of memory?
> Is there a way I can commit after INSERTing every 10000 records?
>
> Thanks in advance

The bulk collect has a "limit" clause, and once collected, obviously a for-all will only process the number of rows in the array.

Its unlikely you'll get much benefit with an arraysize greater than (say) 500, but some quick benchmarking will find the right size for your environment.

If its a one-off operation, create a massive rollback and let it go in one op. If its not a one-off, then you need that size rollback seg anyway, so just create it and keep it

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."