Re: performance tuning question

"Pablo Sanchez" <pablo_at_dev.null> wrote in message news:b3yA8.15$id.27829_at_news.uswest.net...
>
> "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.
>

I have to use Java since this table update is just a part of a more complicated data manipulation process that is implemented in Java, but it is OK since doing only inserts is fast enough.

> 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)
> --

This looks very promising, as the matter of fact I ran it today and it is FAST. There is one problem though, there many more tables participating in the update. The problem is that some of the tables have fields of type LONG RAW which cannot be insert select(ed). I really don't want to vary the data update logic for tables depending on what types they contain, but will probably do that if I can't resolve the problem some other way. I know there's a way to deal with LONGs but I can't use it because of the way we pass queries to Oracle JDBC driver.

It seems that the index on ID field is not being used during select and update statements, is there anything that could cause that?

Thanks,
Vladimir.

> Pablo Sanchez, High-Performance Database Engineering
> mailto:pablo_at_hpdbe.com
> http://www.hpdbe.com
> Available for short-term and long-term contracts
>
>
Received on Sat May 04 2002 - 02:49:43 CDT