Re: performance issue - mass updates

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 10 Nov 2005 08:45:14 +0000 (UTC)
Message-ID: <dkv1ap$40c$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

"niz" <niz_at_infidel.freeserve.co.uk> wrote in message news:1131374260.970937.194120_at_g43g2000cwa.googlegroups.com...
>i have a table containing ~ 100000 rows. it has triggers, contraints
> etc.
> i want to update a single field in ~ 40000 of those rows.
>
> currently i'm using the following sqlplus (not plsql):
>
> UPDATE table SET field2 = date_value WHERE field1 = 9130 and field3 in
> (1190, 1191, 1192, 1193);
> UPDATE table SET field2 = date_value WHERE field1 = 9131 and field3 in
> (1190, 1191, 1192, 1193);
> UPDATE table SET field2 = date_value WHERE field1 = 9131 and field3 in
> (1190, 1191, 1192, 1193);
> .
> .
> .
> <etc 40000 times>
> .
> .
> .
>
>
> field1 is an indexed unique primary key (integer). the 40,000 update
> statements are currently generated via SQL from the same table.
>
> this is proving very slow - over 2 hours runtime. what are the obvious
> ways to speed things up? i am under constraints where i cannot change
> the structure of the table (e.g. adding new indexes or partitioning).
> current development standards also recommend sticking with DML only,
> not committing, and specifying only one exact seqnum for each update.
>
> what can be done to speed things up? will multiple commits help? would
> a single update, instead of 40000 individual ones, help? how much
> difference would removing the field3 clause make? currently
> "date_value" is actually a macro-substituted value (using &). does it
> make a significant difference? would plsql help (obviously it would be
> too big for a single plsql block).
>
>
> any ideas???
>
> thanks.
>
> --
> reply to group
>

From your comments I'm guessing that you have a query that does something like the following:

spool temp.sql

select

    'UPDATE table SET field2 = date_value WHERE field1 = ' ||     tableX.id ||
    'and field3 in (1190, 1191, 1192, 1193)' from

    tableX
where

    {list of conditions}
;

spool off
start temp

If so, you should be looking at something like:

UPDATE table SET field2 = date_value
WHERE
    field3 in (1190, 1191, 1192, 1193);

    field1 in (

                select tableX.id
                from tableX
                where {list of conditions}

    )
;

Depending on actual requirements, you may need to refine the code, or restructure it to optimise the acces - but essentially you can write the update so that the database engine does internally the type of step-wise operation you are doing by hand; and the internal
implementation will be much more efficient than your SQL script.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005

Received on Thu Nov 10 2005 - 02:45:14 CST