Re: Generating Huge Table

as wrote:
> 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;
/ Received on Mon May 23 2005 - 04:06:57 CDT