sqloracle-databaserownumforallbulk-collect

Using BULK COLLECT with rownum


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


Solution

  • 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;