...then we wouldn't have to write code like this, which, as Graham Oakes can confirm, takes ages:
FOR r IN ( SELECT tid FROM t_brel WHERE bqid = iqid )
LOOP
SELECT q.lamount, q.famount
INTO v_lamount, v_famt
FROM t_aq atq
, t_q q
WHERE atq.tid = r.tid
AND q.qid = atq.qid
AND qtype = 10;
v_ltotal := v_ltotal + v_lamount;
v_ftotal := v_ftotal + v_famt;
END LOOP;
UPDATE t_q
SET lamount = v_ltotal
, famount = v_ftotal
WHERE qid = iqid;
We can but dream.
While we're on the subject, we received this from a correspondent who wishes to remain anonymous:
FOR r IN
(
SELECT /*+ FIRST_ROWS */
*
FROM pay_details
WHERE acct_fk = p_accountpk
)
LOOP
DELETE pay_details
WHERE primarykey = r.primarykey;
COMMIT;
END LOOP;
I particularly like the FIRST_ROWS hint. "What, it's slow? Better make sure it uses that index..."
2 comments:
Even better - bulk collect rowids and use forall for some fancy deleting -
jeffkemponoracle.blogspot.com/2006/03/bulk-collect-and-forall-are-faster.html
Not only is that first set of pl/sql code bad because its trying to replace a built-in function, but it is forcing the processing to do a nested loops join.
FOR r IN (
SELECT tid
FROM t_brel r, t_aq atq, t_q q
WHERE tb.BQID = IQID
AND atq.TID = r.TID
AND q.QID = atq.QID
AND QTYPE = 10
)
LOOP
v_ltotal := v_ltotal + v_lamount;
v_ftotal := v_ftotal + v_famt;
END LOOP;
Though the whole thing can be replaced by:
UPDATE t_q t
SET ( lamount, famount ) = (
SELECT
SUM( q.LAMOUNT ),
SUM( q.FAMOUNT )
FROM t_brel r, t_aq atq, t_q q
WHERE tb.BQID = t.QID
AND atq.TID = r.TID
AND q.QID = atq.QID
AND QTYPE = 10
)
WHERE t.QID = iqid
Post a Comment