Re: update extremely slow

From: santosh sharma <no-one_at_yahoo.com>
Date: Wed, 12 Jun 2002 19:08:51 -0700
Message-ID: <ae8v09$4vk3l$1@ID-84096.news.dfncis.de>

I think you can do this without using cursor.

        update A  s set metro_id = 2
        where WHERE   EXISTS
      (SELECT 1      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') and     r.data_id = s.data_id
    )

Please note I have not tested this.

hth
santosh sharma

"charlie.peltier" <charlie.peltier(delete here-antispam)@verizon.net> wrote in message news:svRN8.35769$gs4.2362_at_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 - 21:08:51 CDT