Generating Huge Table

#1

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.

2. The generated target row may or may not be generated before i.e. the same
PK may already be generated by a previous input row.

3. If the PK already generated, I would do an 'update'. If it isnt, I would
do an 'insert'.

In order to verify if the PK've already been generated, my approach is to
issue a 'select pk from target_table' for each generated target row to see
if the PK is there or not.

The problem is, for each input row, the 'select' verification potentially
means scanning the table once. The would mean a huge performance problem.

I am just wondering is there better way to check if the PK already generated
in step 3 above? I am thinking about caching the whole generated target
table in memory so that the duplication checking is done against memory
image, but is there any built-in support provided by JDBC rather than
manually maintaining the cache.

I know nothing about Rowset but heard of Cached RowSet. Is it a cache of db
row that I can use for my requirement here?

Thx for any advice.

#2

as wrote:

Quote:

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.

2. The generated target row may or may not be generated before i.e.
the same
PK may already be generated by a previous input row.

3. If the PK already generated, I would do an 'update'. If it isnt, I
would
do an 'insert'.

In order to verify if the PK've already been generated, my approach
is to
issue a 'select pk from target_table' for each generated target row
to see
if the PK is there or not.

The problem is, for each input row, the 'select' verification
potentially
means scanning the table once. The would mean a huge performance
problem.

I am just wondering is there better way to check if the PK already
generated
in step 3 above? I am thinking about caching the whole generated
target
table in memory so that the duplication checking is done against
memory
image, but is there any built-in support provided by JDBC rather than
manually maintaining the cache.

I know nothing about Rowset but heard of Cached RowSet. Is it a cache
of db
row that I can use for my requirement here?

Thx for any advice.

You can write a procedure that does the insert/update. Do the insert
first... if there is dup_val_on_index exception then do the update.

procedure blah (...,....,....)
is
begin
insert into table ();
exception
when dup_val_on_index then
update table ();
when others then
raise;
end;
/

#3

On 23 May 2005 02:06:57 -0700, "Rauf Sarwar" <rs_arwar (AT) hotmail (DOT) com>
wrote:

Quote:

procedure blah (...,....,....)
is
begin
insert into table ();
exception
when dup_val_on_index then
update table ();
when others then
raise;
end;
/

This does work, but trapping an exception everytime is costly.

--
Sybrand Bakker, Senior Oracle DBA

#4

Sybrand Bakker wrote:

Quote:

On 23 May 2005 02:06:57 -0700, "Rauf Sarwar" <rs_arwar (AT) hotmail (DOT) com
wrote:

procedure blah (...,....,....)
is
begin
insert into table ();
exception
when dup_val_on_index then
update table ();
when others then
raise;
end;
/

This does work, but trapping an exception everytime is costly.

--
Sybrand Bakker, Senior Oracle DBA

How so?

Regards
/Rauf

#5

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

#6

Quote:

I would call this a prime example of something you
do *not* want to do in Java.

--
Regards,
Frank van Bortel

Hi Frank,

Actually, I am considering using Java. My approach is rather primitive (and
welcome suggestion to improve it):
1) read the whole input table into memory (e.g. stored in a Hashmap)
2) generate an in-memory image of the target table and insert the rows when
the in-memory image is ready.

So what is the reason for Java not being the good choice? Is it related to
performance?

#7

"Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> ???
news:d6spvg$ar3$1 (AT) news6 (DOT) zwoll1.ov.home.nl ???...

Quote:

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

By the way, is MERGE an Oracle specific extension to SQL? Is it available
in MSSQL and MySQL? I need to make sure my app is portable. It looks like
availability of MERGE affects the whole design.

#8

"Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> ???
news:d6spvg$ar3$1 (AT) news6 (DOT) zwoll1.ov.home.nl ???...

Quote:

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?

#9

Quote:

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.

Thx,

This description is a bit simplified. The input table is representing a
tree structure and I need to traverse the tree structure and generate 1
output row for each node traversed. So for every row in the input table, it
may, say, generate 10 rows if the row is at level 10 of the hierarchy.

#10

as wrote:

Quote:

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?

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