Oracle optimize select after update status performance

I have a store procedure that will

• Update maximum 500 rows status from 0 to 1
• Return those rows to program via cursor

Here is my store procedure code

PROCEDURE process_data_out (
    o_rt_cursor   OUT SYS_REFCURSOR
) IS
    v_limit       NUMBER;
    l_data_ids      VARCHAR2(32000);
BEGIN
    v_limit := 500;   -- limit 500
    
    l_data_ids := '';
    
    -- Create loop to get data
    FOR i IN (
        SELECT *
                FROM
                    (
                        SELECT id FROM
                            TBL_DATA a
                        WHERE
                            a.created_at BETWEEN SYSDATE - 0.5 AND SYSDATE + 0.1
                            AND a.status = 0
                            AND a.phone NOT IN (SELECT phone FROM TBL_BIG_TABLE_1)
                            AND a.phone NOT IN (SELECT phone FROM TBL_BIG_TABLE_2 WHERE IS_DENY = 1)
                        ORDER BY
                            priority
                    )
                    WHERE
                        ROWNUM <= v_limit
    ) LOOP
        BEGIN
        
            -- Build string of ids like id1,id2,id3,
            l_data_ids := l_data_ids
                        || i.id
                        || ',';
                        


            -- update row status to prevent future repeat
            UPDATE TBL_DATA
                SET
                    status = 1
                    WHERE
                        id = i.id;

        END;
    END LOOP;

    COMMIT;
    
    
    -- If string of ids length >0 open cursor to take data
    
    IF ( length(l_data_ids) > 0 )
    THEN
        -- Cut last comma id1,id2,id3, --> id1,id2,id3
        l_data_ids := substr(l_data_ids,1,length(l_data_ids) - 1);

        -- open cursor 
        OPEN o_rt_cursor FOR 
            SELECT
                id,
                phone
                    FROM
                        TBL_DATA a
                    WHERE
                        a.id IN (
                            SELECT
                                to_number(column_value)
                            FROM
                                XMLTABLE ( l_data_ids )
                        );

    END IF;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
       
END process_data_out;

I want to optimize this performance and here is my question

1. Should I replace in by exists

Replace

AND a.phone NOT IN (SELECT phone FROM TBL_BIG_TABLE_1)
AND a.phone NOT IN (SELECT phone FROM TBL_BIG_TABLE_2 WHERE IS_DENY = 1)

by

AND NOT Exists (SELECT phone FROM TBL_BIG_TABLE_1 where TBL_BIG_TABLE_1.phone = a.phone)
AND NOT Exists (SELECT phone FROM TBL_BIG_TABLE_2 WHERE TBL_BIG_TABLE_2.phone = a.phone and IS_DENY = 1)

2. Is there a better way than

   Save a string of ids like id1,id2,id3 after update row status

   Open cursor by select from string of ids

I appreciate for any suggestion.

Thank for your concern

Answer

Row-by-row processing is always slower and you are also creating the string of ids, which again takes time so overall performance is going down.

You can use the collection DBMS_SQL.NUMBER_TABLE to store the updated ids from the UPDATE statement using the RETURNING clause and use it in the cursor query.

Also, I have changed your update statement so that it does not use NOT IN and uses the LEFT JOINS and ROW_NUMBER analytical function for increasing the performance as follows:

CREATE OR REPLACE PROCEDURE PROCESS_DATA_OUT (
    O_RT_CURSOR OUT SYS_REFCURSOR
) IS
    V_LIMIT      NUMBER;
    L_DATA_IDS   DBMS_SQL.NUMBER_TABLE;
BEGIN
    V_LIMIT := 500;   -- limit 500
    
    UPDATE TBL_DATA A
       SET A.STATUS = 1
     WHERE A.ID IN (
        SELECT ID
          FROM ( SELECT ID,
                        ROW_NUMBER() OVER(ORDER BY PRIORITY) AS RN
              FROM TBL_DATA          B
              LEFT JOIN TBL_BIG_TABLE_1   T1 ON T1.PHONE = B.PHONE
              LEFT JOIN TBL_BIG_TABLE_2   T2 ON T2.IS_DENY = 1 AND T2.PHONE = B.PHONE
             WHERE B.CREATED_AT BETWEEN SYSDATE - 0.5 AND SYSDATE + 0.1
               AND B.STATUS = 0
               AND T1.PHONE IS NULL
               AND T2.PHONE IS NULL)
         WHERE RN <= V_LIMIT ) RETURNING ID BULK COLLECT INTO L_DATA_IDS;

    OPEN O_RT_CURSOR FOR SELECT ID, PHONE
                           FROM TBL_DATA A
                          WHERE A.ID IN (SELECT COLUMN_VALUE FROM TABLE ( L_DATA_IDS ));

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END PROCESS_DATA_OUT;
/