Tunning help for plsql
Date: 25 Mar 2002 16:15:44 -0800
Message-ID: <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
CURSOR ccrn(v_pidm NUMBER, v_term VARCHAR2) IS
select distinct pidm, term_code, tckn_crn
from sytihst_bak
--about 60K rec in here
select pidm, term_code, record_type, tckn_crn, tckn_seq_no,
checkseq
from sytihst_bak
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;
DECLARE
where pidm=v_pidm
and term_code = v_term;
CURSOR cseq(v_pidm NUMBER, v_term VARCHAR2, v_crn VARCHAR2) IS
where pidm=v_pidm
and term_code = v_term
and tckn_crn = v_crn
for update of tckn_seq_no, checkseq;
w_seq INTEGER;
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 Tue Mar 26 2002 - 01:15:44 CET