Using BULK COLLECT with rownum

Question

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 definitely should not use rownum for updates :) Use your primary or other unique keys — Sudipta Mondal, Jun 26 '18 at 07:05
Thanks Sudipta for your response.In SQL select query , I am getting result into some order , that order I need to update in one of the column.I do not think that Ican achieve same thing from primary or other keys. :) — Khushi, Jun 26 '18 at 07:09
If you want an order, use a rank function. I would suggest you to reconsider using rownum. you should read about it on ASK TOM or other Stackoverflow question — Sudipta Mondal, Jun 26 '18 at 07:10
Yes you can use `rownum` or anything else you want. What isn't working? — William Robertson, Jun 26 '18 at 07:26
You have made a comment addressed to me on Connor's answer. Just to answer your question, yes, `ROWID` would be same even if we order the table records/rows. A rowid uniquely identifies the address of each row and **not** generated in runtime like `ROWNUM`. google rowid for Oracle to understand more. — Kaushik Nayak, Jun 26 '18 at 18:48

Kaushik Nayak · Accepted Answer · 2018-06-27T02:34:58.543

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;

Thank you Kaushik, I have tested your query .It is faster and results are consistent with original query. — Khushi, Jul 02 '18 at 12:27

score 0 · Answer 2 · answered Jun 26 '18 at 08:16

You can grab the rowid along with the initial select, dump the lot into arrays and then use that in your FORALL, eg

declare
  l_num_list sys.odcinumberlist := sys.odcinumberlist();
  l_char_list sys.odcivarchar2list := sys.odcivarchar2list();
begin  
SELECT rownum rn,rowidtochar(rid)
bulk collect into l_num_list, l_char_list
FROM
            (SELECT a.*, rowid rid
            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;

forall i in 1 .. l_num_list.count
            UPDATE temp_final
            SET horder=l_num_list(i)
            WHERE rowid = chartorowid(l_char_list(i));

end;
/

@Kaushik Nayak ,thanks for your response. Just to understand about your 'on' criteria, rowid would be same even if we order the table records/rows? I just want to check whether your query would update the same rows which are intended. Thanks. — Khushi, Jun 26 '18 at 12:47
Thanks Connor once again .I am accepting your answer though i will be using Merge Into as number of rows will be very less here around 250~ so I think forall execution time would be almost same. — Khushi, Jul 02 '18 at 12:33

Using BULK COLLECT with rownum

2 Answers2