Question to AskTom on Oracle Magazine

Just threw a random question to Tom after read his column in Oracle Magazine, to my surprise, Tom replied, and it made me happy for a couple weeks.
AskTom link www.oracle.com/technetwork/issue-archive/2011/11-may/o31asktom-354139.html

My question to Tom,

Just read you Q&A on "Bulking Up" on May 2011 Oracle Magazine.


It looks like UPDATE is different than INSERT when comparing PL/SQL BULK to single SQL.


Single SQL INSERT is about same as BULK INSERT when comparing the resource usage,
But based on your benchmark, Single SQL UPDATE is way more better than array bulk UPDATE.
I'll build a test case and run benchmark of UPDATE myself and blog it later.


FYI, single SQL INSERT vs. array BULK INSERT.
http://mujiang.blogspot.com/2009/06/single-insert-vs-array-bulk-insert.html

Replied by Tom, on 31-May-11 12:46 PM, opubedit wrote:

Charlie,

With the single insert,  you can easily use /*+ APPEND */ - allowing for direct path load, basic compression (comes with enterprise edition), and redo/undo generation being skipped.

with the bulk insert - until 11.2 - you cannot.  In 11.2 you can use the append_values hint to do the direct path load, but then you have to size your batches very carefully (since direct pathing never reuses existing space, you'll have "chunks of data")

Further, you show a measurable different in cpu/elapsed time even without these other advantages.

I will stick with my advice of "do it in a single sql statement if you can, if not do it in as little procedural code as possible".  Unless there is no way to do it in a single insert as select - there should be NO procedural code whatsoever.


My reply:

Hi Tom,

Thanks for the reply. I implement your mantra all the time, I’m thrifty to develop procedure code out of single SQL.

Context is the king.

. Our data process logic cannot be done in single SQL.

 .. We will update different target table columns based different comparison scenarios, with different source data,

(I asked a question on AskTom, about how to only update the columns with changed value.  J

  e.g. 70% of source data only update book price or qty.

  it’s one of the update scenario. )

 .. We will insert intermediate processed data to many staging tables based the each comparison scenarios,

 .. I tried multi-table INSERT, it still couldn’t solve our problem.

. Our primary database is forced logging to support standby database.

So, I did a benchmark to see how the bulk INSERT can be worse. The bulk UPDATE vs. single SQL UPDATE will be benchmarked too in near future.

Thanks,

Charlie 木匠 | Data Solution Architect/Developer

- simple is beautiful and please consider the environment before printing this e-mail.