Re: I need to update 30.000 rows - it must be fast.

On Thu, 07 Mar 2002 05:42:58 -0500, Martin Forsberg wrote:

> Here is my problem:
>
>
> An application updates a table in Oracle (8.17 on win2000).
>
> The application comunicate with Oracle trough ODBC.
>
> Every second minute the value is updated for 30.000 diffrent id's.
>
> I must check if the VALUE is between MIN AND MAX. If the value is out of
> range the
>
> value must be inserted anyway, but I need to know if the value is out of
> range.
>
>
> TABLE_1
> ---------
> ID
> MAX
> MIN
> VALUE
> ALARM (Now I use a trigger: ALARM = 1 if VALUE out of range)
>
>
> Is there a smarter solution? I must be very fast.
>
>
>
> /Martin

Try partitioning and parallel DML. In that case the speed of the update depends only on the power of the box. I'm fairly certain that a 64 CPU HP SuperDome with 256GB RAM and EMC disk array with 32GM cache can do that in less then a second, especially if the whole table is the keep buffer cache and if the table is well partitined so that, say, 48 CPUs can be used. The price of the box and the accompanying licenses (OmniBackup, EMC, Oracle) is not for the ones with faint heart. You can get similarly priced R6000 box from IBM, wwith the similar characteristics or SUN. It is as in that Taco Bell commercial from the time when the Godzilla movie was around: You need a bigger box.....
Oh yes, did I mention solid state disks for redo logs? That really helps to speed up commits....
Humor aside, you need to evaluate CPU power and IO capacity of your box. Oracle tuning can only go to certain extent. The brute force of the box is still impossible to replace. I'm quite confident that there is a box between the SuperDome or SUN Fire and a Xeon PC that could do what you need it to do.

-- 
Mladen Gogala