Re: Oracle Batch Update Problem ..

On Mon, 07 May 2007 21:18:53 -0700, rahul.vakil wrote:

> Surprisingly, the
> update is taking more 3 to 4 times more then insert.

Why would that be surprising? If you're doing direct insert (/*+ APPEND */), all that oracle needs to do is to allocate new blocks behind the HWM and insert them. With updates, oracle must locate the existing rows and update them. If you didn't create table with large enough PCTFREE, then row chunks are linked and you get the beauty known as "chained rows", particularly good for query performance.

>
> Now, I am using the above update + insertion logic over multiple 4k
> chunks and time taken for update + insert over all chunks is not same;
> it varies for each batch.

Oracle has something called MERGE (not "merde", as some of my Canadian friends are sometimes tempted to say) which does precisely that. Timings will vary, depending on the underlying operation. To improve timing, buy a large IBM and equip it with EMC with 128G of NVRAM. IBM handles I/O extremely well and EMC Symmetrix with 128G of NVRAM is extremely fast at it.

-- 
http://www.mladen-gogala.com