Is there a way to loop through cursor faster in oracle?-CodePudding

I need to insert a big amount of data in a table from a file, but the data need to be checked if will be inserted or updated.

My program works like this

PROCEDURE INSERT_OR_UPDATE
IS
    [...]

BEGIN

    READ_FROM_FILE_AND_INSERT_ON_SUPPORT_TABLE;

    DECLARE
        CURSOR C1 IS
            SELECT ROWID ROW_ID, A.* FROM SUPPORT_TABLE A;

    BEGIN
        FOR RECORDS IN C1 LOOP
            BEGIN
                SELECT COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5
                INTO   vVARIABLE1, vVARIABLE2, vVARIABLE3, vVARIABLE4, vVARIABLE5
                FROM TABLE1 A, TABLE2 B
                WHERE A.COLUMN1 = B.COLUMN1
                AND   A.COLUMN2 = B.COLUMN2
                AND   A.COLUMN3 = B.COLUMN3
                AND   A.COLUMN4 = B.COLUMN4
                AND   A.COLUMN1 = RECORDS.COLUMN1
                AND   A.COLUMN3 >= TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'));

                IF
                    RECORDS.COLUMN1  = vVARIABLE1
                    AND  W1.COLUMN2  = vVARIABLE2
                    AND  W1.COLUMN3  = vVARIABLE3
                    AND  W1.COLUMN4 >= TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD')
                THEN
                    UPDATE SUPPORT_TABLE
                    SET STATUS_MESSAGE = 'TO UPDATE',
                    STATUS = 'PROCESSED',
                    WHERE ROWID = RECORDS.ROW_ID;

            EXCEPTION WHEN NO_DATA_FOUND THEN
                UPDATE SUPPORT_TABLE
                SET STATUS_MESSAGE = 'TO INSERT',
                STATUS = 'PROCESSED'
                WHERE ROWID = RECORDS.ROW_ID;
        END LOOP;
    END;
END;

The problem is, the performance of the code is very awful. After run by 4 hours it only processed 0.05% is there a way to optimize the process?.

Obs.: The file have 654.000 lines.

CodePudding user response：

You appear to want a single MERGE statement:

CREATE PROCEDURE INSERT_OR_UPDATE
IS
  W1 TABLE1%ROWTYPE; -- Define W1 as something
BEGIN
  READ_FROM_FILE_AND_INSERT_ON_SUPPORT_TABLE;

  MERGE INTO support_table dst
  USING (
    SELECT s.ROWID AS rid,
           a.COLUMN1,
           a.COLUMN2,
           a.COLUMN3,
           a.COLUMN4,
           COLUMN5
    FROM   support_table s
           LEFT OUTER JOIN (
             TABLE1 A
             INNER JOIN TABLE2 B
             ON (   A.COLUMN1 = B.COLUMN1
                AND A.COLUMN2 = B.COLUMN2
                AND A.COLUMN3 = B.COLUMN3
                AND A.COLUMN3 >= TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'))
                AND A.COLUMN4 = B.COLUMN4
                )
           )
           ON (A.COLUMN1 = s.COLUMN1)
  ) src
  ON (src.rid = dst.ROWID)
  WHEN MATCHED THEN
    UPDATE
    SET status_message = CASE
                         WHEN src.COLUMN1 IS NOT NULL
                         THEN 'TO UPDATE'
                         ELSE 'TO INSERT'
                         END,
        status         = 'PROCESSED'
    WHERE src.COLUMN1 IS NULL
    OR    (   W1.COLUMN2  = src.COLUMN2
          AND W1.COLUMN3  = src.COLUMN3
          AND W1.COLUMN4 >= TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'))
          );
END;
/

(Note: this code is untested as no CREATE TABLE or INSERT statements were provided for your sample data nor any expected output so it is unclear what your expected behaviour would be.)

db<>fiddle here

CodePudding user response：

Use Merge for this, it's efficient and covers your needs:

MERGE INTO TABLE1 A
USING (SELECT *
         FROM SUPPORT_TABLE) records
   ON (
        RECORDS.COLUMN1 = A.COLUMN1
      )
 WHEN MATCHED THEN UPDATE SET <A.COLUMN(S) = records.columns>
 WHEN NOT MATCHED THEN INSERT INTO A VALUES records;

(this is pseudo since you haven't specified the destination table neither the table descriptions)