help: update extremely slow

From: charlie.peltier <here-antispam)_at_verizon.net>
Date: Thu, 13 Jun 2002 00:16:24 GMT
Message-ID: <svRN8.35769$gs4.2362@nwrddc02.gnilink.net>

I have a pl/sql which will update a table as follows:

CURSOR C1 is select
r.data_id id
from
A partition ( jan_2001 ) r
where r.polltime > to_date('01/01/2001 00:00:00','mm/dd/yyyy hh24:mi:ss') AND
r.polltime < to_date('02/01/2001 00:00:00','mm/dd/yyyy hh24:mi:ss') ;

loop_counter NUMBER := 0;

BEGIN FOR rec in C1 LOOP

        loop_counter := loop_counter + 1;
        update A set metro_id = 2
        where data_id= rec.id;

        IF loop_counter > 5000 THEN
                loop_counter := 0;
                COMMIT;
        END IF;

end loop;
commit;
end;

There are two indexes on the table, but none of them on metro_id, so I guess it should not be the reason to slowing down. Any suggestions will be highly appreciated.

Thanks Received on Wed Jun 12 2002 - 19:16:24 CDT