Tuesday, June 07, 2011

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.


Tuesday, May 10, 2011

Explore MongoDB

Here we go,

http://mongly.com/tutorial/index


Bravo!
Hopefully you learnt something about MongoDB and Unicorns
There's a lot we didn't cover, like embedded documents, aggregation, indexes, management, etc
But the idea was to help get you confortable with the basics
Feel free to play around, insert new data, new combinations, and what not
Or, better yet, head over to the mongodb download page, grab the version for your OS and experiment on your local machine
The precompiled binaries available for Linux, Windows and OS X make MongoDB extremely easy to get started with
Don't forget to check out my free MongoDB ebook
You are now web scale

db.unicorns.find() 
_id
name
loves
dob
weight
gender
vampires
4dc9bbfbbc7a0515660052c8
Aurora
["carrot","grape"]
1991-01-25T13:14:00Z
450
f
43
4dc9bbfbbc7a0515660052cb
Solnara
["apple","carrot","chocolate"]
1985-07-05T02:01:00Z
550
f
80
4dc9bbfbbc7a0515660052cc
Ayna
["strawberry","lemon"]
1998-03-08T08:30:00Z
733
f
40
4dc9bbfbbc7a0515660052cf
Leia
["apple","watermelon"]
2001-10-09T14:53:00Z
601
f
33
4dc9bbfbbc7a0515660052d1
Nimue
["grape","carrot"]
1999-12-20T16:15:00Z
540
f
49
4dc9bbfbbc7a0515660052d3
Lois
55
f
0
4dc9bbfbbc7a0515660052c7
Horny
["carrot","papaya","orange"]
1992-03-14T07:47:00Z
600
m
63
4dc9bbfbbc7a0515660052c9
Unicrom
["energon","redbull","orange"]
1973-02-10T22:10:00Z
984
m
182
4dc9bbfbbc7a0515660052ca
Roooooodles
["apple","orange"]
1979-08-18T18:44:00Z
575
m
99
4dc9bbfbbc7a0515660052cd
Kenny
["grape","lemon","orange"]
1997-07-02T10:42:00Z
690
m
39
4dc9bbfbbc7a0515660052ce
Raleigh
["apple","sugar","orange"]
2005-05-04T00:57:00Z
421
m
2
4dc9bbfbbc7a0515660052d0
Pilot
["apple","watermelon","orange"]
1997-03-27T05:03:00Z
650
m
54
4dc9bbfbbc7a0515660052d2
Dunx
["grape","watermelon","orange"]
1976-07-19T19:18:00Z
704
m
165