Re: How do I speed up updates

'Drop all indexes' is NOT a good idea.

Look at what the code is trying to do -
If is running from Pro*C and is executing a simple (possibly single row) update
a large number of times.

Looking at the column names

1. id is likely to be indexed, the note implies it is the key, and needs to stay indexed
2. value is highly unlikely to be indexed
3. last_use may be indexed, and an improvement in speed could be achieved by dropping indexes in which it appears, but that may not be a viable option anyway.

In answer to the original question - creating a view will not make a difference to performance.

Make sure, first, that Oracle is using the index to do the update, and not doing a tablescan for each row in the array.

How many rows per second are you getting at present ? It's hard to improve if we don't have a baseline.

How are you generating the values that go into the arrays ?

One of the significant costs of operations like this is the large overhead for each small update. There is a mechanism in 8.1.6 for PL/SQL (the FORALL clause) which reduces the overhead for exactly this type of operation.

If you are stuck with 8.1.6, you might consider doing a simple 'insert into holding table' of the three arrays, then updating the main table from the holding table as a single update. (There is a note on my website about the most efficient way of doing this with updateable join views).

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Robert Malikian wrote in message
<3g2U5.825$EE.24849_at_news-1.opaltelecom.net>...


>Drop all indexes and do the update then create the indexes.

>Robert

>"Iain Wiseman" <bibble_at_ibm.net> wrote in message

>news:3a1f05ef_4_at_news1.prserv.net...

>> I would like to update two columns in a table in bulk the fastest way

>> possible. In general we do not have any insert or deletes, just updates.

>> There are few indexes over this table as well.

>>

>> We have made the initial extent a quarter of the expected total size.

 (3m)


>>

>> What is the quicket way to do this ?

>>

>>   EXEC SQL

>>       for :rowCount

>>       UPDATE

>>          MY_TABLE

>>       SET

>>          value          = value - :DeltaValue,

>>          ba.last_use = SYSDATE

>>       WHERE

>>          id = :myId;

>>

>> I am currently using array updates but the CPU usage is very high. Are

 there


>> ways to create views that contain just the key and the two columns and is

>> this efficient ? What are my options. (This is currently in ProC).

>>

>> We are looking to process over 200+ on an POLO running HPUX 10 something

>> and oracle 8.0.6