as (none (AT) asgmeail (DOT) com) wrote:

: "Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> ???
: news:d6spvg$ar3$1 (AT) news6 (DOT) zwoll1.ov.home.nl ???...
: > Sounds like an *excellent* case for the merge
: > statement.
: > And Thomas Kyte has some benchmarks on insert... exception
: > vs merge (also on insert/update vs update/insert).
: >
: > I would call this a prime example of something you
: > do *not* want to do in Java.
: >
: > --
: > Regards,
: > Frank van Bortel

: Another concern is MERGE expects a *TABLE* of rows to be merged into the
: target table. But the point is my java program generates 1 *ROW* at a time.
: How should I utilitised the MERGE statement? Is it by creating a temporary
: table an do a MERGE for, say, every 10 rows generated?

I though you said

"I am using JDBC to access Oracle from Java program. I need to
generate a huge table from reading a another huge input table.
Here is the requirement:

1. Read a row from the input table, do calculation, generate a
target row.

so surely you do have an input table.


--

This space not for rent.

as wrote:

The point would be to try and do this all in SQL with perhaps a PL/SQL
table function. Query the initial table via a table function, that
applies the calculation/mapping that you want to do, and use the result
set from this in the MERGE with the existing table. It will probably all
boil down then to single SQL statement which the engine will process for
you. See the Oracle By Example on OTN, specifically
http://www.oracle.com/technology/obe...dwh/index.html

I reference the following sections
Using the New Upsert Functionality, SQL MERGE Keyword Overview
Table Function Overview

as wrote:

Oh my god - here we go again

You know, there's an excellent opportunity to make your
application portable, and it's called cards. Just write on 'em, and you
can take them anywhere. True portability! Truly lightweight! Minimal
cost - low TCO! How's that for marketing?

STOP treating databases like bins with data, and start using them.
They way you envision your app is no different than using flat files;
very portable, too!

START reading about the specifics of each underlying engine, and start
using that - why would you buy the 7.3 liter/630BHP top-of-the-bill
car, when you plan not to go any faster tha 30Mph? The 1 liter/40Bhp
20 times cheaper model could have done that, too.

How about an API, specific for each engine, and make the calls to that
API universal. I know - it doesn't give you the opportunity to show off
with some beta demo on the short term, but it's pretty universal on
the longer term. And -once the customers get the idea- it will get you
credibility, as you actually have to think this through!

To the problem at hand: write a procedure that will do the job - it's
database based, so no round trips over a slow network. It's SQL, and
it's set processing, so it's *fast*.
Call the procedure from your java program. MSSQL supports procedures,
and so does MySQL.
Get a team of Oracle PL/SQL, MS TSQL and MySQL programmers; you do
the Java stuff you're good at - they do the stuff they're good at.

My 3c
--
Regards,
Frank van Bortel