Home » RDBMS Server » Performance Tuning » How to make this query run faster using PL/SQL update and insert
How to make this query run faster using PL/SQL update and insert [message #132248] Thu, 11 August 2005 09:46 Go to next message
Rathor
Messages: 5
Registered: August 2005
Junior Member
Hi,

Any idea on how to make this faster (oracle 7), please!



DECLARE
V_TEMP VARCHAR2(10);
V_TRAP_ERROR EXCEPTION;

CURSOR c1 IS
SELECT a.emp_code
,a.action
,e.seq_no
,e.rowid
FROM code_table a
,emp1_table e
WHERE e.emp_id = '&1' AND
e.emp_code = a.emp_code;

v_c NUMBER(3) := 0;


BEGIN
FOR cur IN c1
LOOP

if cur.action = 'D' THEN

BEGIN
UPDATE emp1_table e
SET e.TOTAL = NULL
,e.emp_grade = 'X'
,e.timestamp = SYSDATE
WHERE e.emp_id = '&1' AND
e.rowid = cur.rowid;

EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE V_TRAP_ERROR;
END;

v_c := v_c + 1;

BEGIN
UPDATE emp2_table e
SET e.tier = 3
,e.timestamp = SYSDATE
WHERE e.emp_id = '&1'
AND e.seq_no = cur.seq_no;


EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE V_TRAP_ERROR;
END;

BEGIN

INSERT INTO hist_emp_table
(emp_id,
emp_total,
emp_seq_no,
emp_tier,
emp_timestamp
)
SELECT e.emp_id,
e.total,
e.seq_no,
e.tier,
sysdate
FROM emp2_table e
WHERE e.emp_id = '&1'
AND e.seq_no = cur.seq_no;

EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE V_TRAP_ERROR;
END;

END IF;

IF v_c = 1000 THEN
COMMIT;
v_c := 0;
END IF;

END LOOP;
COMMIT;

EXCEPTION WHEN V_TRAP_ERROR THEN
ROLLBACK;
END;
/

Re: How to make this query run faster using PL/SQL update and insert [message #132288 is a reply to message #132248] Thu, 11 August 2005 13:58 Go to previous message
Mahesh Rajendran
Messages: 10702
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
you start by commiting out of the loop.
One final commit is better than multiple commits.
Make sure have the right indexes.
Make sure you have analyzed the tables and indexes.
Previous Topic: how to get full version of sql statement
Next Topic: B*-tree or bitmap index?
Goto Forum:
  


Current Time: Fri May 27 18:08:39 CDT 2022