I spent the last 2 weeks in Boston working with the oracle coherence team and how that product works and I am impressed with it. One thing which I was keen on testing out / learning in depth was the read-write-backing-map-scheme. I specifically wanted to learn write-behind which when enabled the cache will delay writes to the back end cache store, which in my example would be an Oracle 11g database. To me there is really only 2 options for this to be as quick as possible and of course use connection pooling which UCP (Oracle Universal Connection Pool) is perfect for.
1. JDBC Batching
2. PLSQL bulk binds
Whats important though is that we only commit after X amount of records, and only go to the database when we are ready to insert those X amount of records in a single round trip. To me PLSQL bulk binds make sense here, as all it takes is to convert those cache entries into an SQL object which the database can interpret from PLSQL. Once that's done your PLSQL is as simple as this really.
FORALL i IN mid.FIRST..mid.LAST
insert into messages (message_id, message_type, message)
values (mid(i), mt(i), m(i));
I will post a full example on this shortly which also shows how to read the data back into the cache.
More about this can be found here on what I want to setup. My initial testing showed 1,000,000 records inserted without too much issues BUT there are a couple of things to be aware from coherence and oracle itself.