Re: How to Analyze the performance of "for loop" ?

In message <c3d4638a.0112071156.49bd0085_at_posting.google.com> , Vance Wu <vwu_at_anacomp.com> writes
>Hi,
>
>I have a segment of script to update table p_types:
>
>DECLARE
> CURSOR c_p IS
> SELECT p_id, p_eff_date
> FROM p
> ORDER BY p_id;
>
> rec_p c_p%ROWTYPE;
> v_count number := 0;
>
>BEGIN
> FOR rec_p IN c_p LOOP
>
> UPDATE p_types
> SET p_eff_date = rec_p.p_eff_date
> WHERE p_id = rec_p.p_id;
>
> v_count := v_count + 1;
>
> IF v_count >= 10000 THEN
> v_count := 0;
> COMMIT;
> END IF;
>
> END LOOP;
>
> COMMIT;
>END;
Does this actually work? It looks to me as if you should get a ORA-01002 - 'fetch out of sequence' because you are committing in the middle of a cursor for loop.

Perhaps you development database has less than 10,000 rows?

Perhaps your production database is taking 18 hours to do less than 10,000 rows?

Andy

-- 
Andy Hardy. PGP ID: 0xA62A4849
===============================================================