Below query does Bulk select and then update the records in loop. I want to use BULK COLLECT INTO and FORALL constructs here to improve performance.But query uses rownum to update a column in loop. Is there anyway I can use BULK COLLECT INTO while getting rownum as well?
FOR rec IN
(SELECT rownum rn,
b.*
FROM
(SELECT *
FROM temp_final a
WHERE reid = 1
AND retype = 9
AND sid = 'r123'
AND pid = 2191
ORDER BY a.horder DESC nulls last,
sname ,
rowgroup ,
dpct DESC nulls last ,
name
) b
)
LOOP
UPDATE temp_final
SET horder=rec.rn
WHERE reid = 1
AND retype = 9
AND sid = 'r123'
AND pid = 2191
AND mid =rec.mid;
END LOOP;
Thanks
You don't need a bulk collect, what your pl/sql update is trying to do can be rewritten as a single MERGE INTO
statement, which would be much efficient than using FORALL
. If you still insist on using forall, you may convert this MERGE into a forall block.
MERGE INTO temp_final tgt USING (
SELECT rowid,
ROW_NUMBER() OVER(
ORDER BY
horder DESC NULLS LAST,sname,rowgroup,dpct DESC NULLS LAST,name
) rn
FROM
temp_final
WHERE
reid = 1
AND retype = 9
AND sid = 'r123'
AND pid = 2191
)
src ON ( tgt.rowid = src.rowid )
WHEN MATCHED THEN UPDATE SET tgt.horder = src.rn;