Re: Tunning help for plsql

update checkseq a
set

    tckn_seq_no = (select r-min_r+1 from

                  (
                            select r,pidm,term_code_eff,tckn_crn,min(r) over
(partition by pidm,term_code_eff) min_r from
                            (select rownum r,pidm,term_code_eff,tckn_crn
from sztstdn,
                                                                (select
distinct pidm, term_code, tckn_crn  from sytihst_bak
                                                                where
pidm=pidm  and term_code = term_code_eff) x
                            )
                ) b
                where a.pidm=b.pidm and a.term=b.term_code_eff and

    checkseq = 'Y'

"Helen" <hhuynh99_at_yahoo.com> wrote in message news:c4d1ed11.0203251615.62120a_at_posting.google.com...
> Hi, I need to reset the tckn_seq_no so that it's unique per
> pidm/term/tckn_crn.
>
> This script works but it's so slow. Any advice?
> Thanks in advance
>
> -------------------------------------
> DECLARE
>
> -- about 30K recs in here
> CURSOR c1 IS
> SELECT pidm, term_code_eff
> FROM sztstdn;
>
> CURSOR ccrn(v_pidm NUMBER, v_term VARCHAR2) IS
> select distinct pidm, term_code, tckn_crn
> from sytihst_bak
> where pidm=v_pidm
> and term_code = v_term;
>
> --about 60K rec in here
> CURSOR cseq(v_pidm NUMBER, v_term VARCHAR2, v_crn VARCHAR2) IS
> select pidm, term_code, record_type, tckn_crn, tckn_seq_no,
> checkseq
> from sytihst_bak
> where pidm=v_pidm
> and term_code = v_term
> and tckn_crn = v_crn
> for update of tckn_seq_no, checkseq;
>
>
> w_seq INTEGER;
>
>
> BEGIN
>
> FOR c1_cur IN c1 LOOP
>
> FOR ccrn_cur IN ccrn(c1_cur.pidm, c1_cur.term_code_eff) LOOP
>
> FOR cseq_cur IN cseq(ccrn_cur.pidm, ccrn_cur.term_code,
> ccrn_cur.tckn_crn) LOOP
>
> w_seq := ccrn%ROWCOUNT;
>
> UPDATE sytihst_bak
> SET tckn_seq_no = w_seq,
> checkseq = 'Y'
> WHERE current of cseq;
>
>
> END LOOP;
> COMMIT;
>
> END LOOP;
>
> END LOOP;
>
> EXCEPTION
> WHEN OTHERS THEN
> dbms_output.put_line(SUBSTR(SQLERRM,1,2000));
> ROLLBACK;
>
> END;
> /
Received on Thu Mar 28 2002 - 16:21:32 CET