Re: performance tuning question

"Vlad G" <vladgri_at_NO_hotmail_SPAM.com> wrote in message news:fipA8.4452$M7.1805321_at_twister.socal.rr.com...
> Hi there,
>
> I'm looking for an advice what configuration parameters I need to
look at
> first to boost Oracle 8i performance for the following simple
scenario. I'm
> doing a batch update of a table containing about 65k records. The
table
> structure is rather simple:
>
> MY_TABLE: ID, COL1, COL2, COL3
>
> all fields are integers except COL3 which is VARCHAR 2000. There is
no
> primary key set on the table during update. The program is Java and
its
> logic is (in pseudocode):
>
> for (each id in update)
> {
> if (exists(SELECT * FROM MY_TABLE WHERE ID=id))
> UPDATE MY_TABLE SET COL1=val1, COL2=val2, COL3=val3 WHERE
ID=id
> else
> INSERT INTO MY_TABLE id,val1,val2,val3
> }
>

Hi Vladimir,

The issue with the above is that you're forcing row at a time processing. This will hurt any database: Oracle, MS SQL Server, DB2, Sybase ... If you do a simple re-write you can get the work done, in at _least_ 1/10th the time. My guess is that it'll take perhaps five mintutes. :)

The algorithm for you to implement is:

1. Load all the new data into a new table using SQL*LOADER -- research it to find the quickest way to load your table. The new table should have the 'no_logging' option set when it's initially created. Let's call it DATA_2_LOAD Create the index on 'id' _after_ you've loaded the table.
2. With the data loaded, it's relatively easy to either UPDATE or INSERT. The idea is to create a set of data that will be INSERT'd in one fell swoop and a set of data that will be UPDATE'd that way as well. The rough outline is as follows:

    INSERT INTO MY_TABLE ....

        SELECT * FROM DATA_2_LOAD D2L
        WHERE D2L.ID NOT IN (SELECT ID FROM MY_TABLE)

    UPDATE MY_TABLE M
         SET (COL1, COL2, COL3) = (SELECT ... FROM DATA_2_LOAD D2L
WHERE D2L.ID = M.ID)
       WHERE M.ID IN (SELECT ID FROM DATA_2_LOAD)

--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo_at_hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts