How can i optimize an oracle procedure used to copy data from tables?

Question

I have two different tables in my oracle database. I would copy only part of data of my first table (ANOTHER_TABLE), selecting only some column and filtering the rows, into a new one (NEW_TABLE).

I tried with the following procedure, using cursor, but i registers long execution time. How can i optimize this oracle sql procedure? It is possible?

For information, the ANOTHER_TABLE contains about 500k records.

PROCEDURE IMPORT_DATA
  AS
  BEGIN
    DECLARE
      c_FIELD1 ANOTHER_TABLE.FIELD1%type;
      c_FIELD2 ANOTHER_TABLE.FIELD2%type;
      row_found NUMBER;
      CURSOR c
      IS
        (
        -- choose only valid data
        SELECT FIELD1, FIELD2
        FROM ANOTHER_TABLE
        WHERE FIELD2 = '1'
        ) ;
    BEGIN
      OPEN c;
      LOOP
        FETCH c INTO c_FIELD1, c_FIELD2;
        EXIT
      WHEN c%notfound;
        BEGIN
          -- verify the record existance to decide if it is 
          -- necessary an update or an insert operation
          SELECT 1
          INTO row_found
          FROM NEW_TABLE
          WHERE FIELD1 = c_FIELD1;
          -- update record
          UPDATE NEW_TABLE
          SET FIELD2 = c_FIELD2
          WHERE FIELD1 = c_FIELD1;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
          -- insert record
          INSERT
          INTO NEW_TABLE
            (
              FIELD1,
              FIELD2
            )
            VALUES
            (
              c_FIELD1,
              c_FIELD2
            );
        WHEN TOO_MANY_ROWS THEN
          -- duplicated record
          -- show error!
        END;
      END LOOP;
      CLOSE c;
      COMMIT;
    END;

I would copy only same data of one table into another one in the fastest way. How can i optimize it?

Thank you

Answer 1

In general, the fastest way to do things is with a single query, not with cursors. If you want to insert new, distinct values into NEW_TABLE (as the title suggests), then you can do this with a single INSERT:

INSERT INTO NEW_TABLE(FIELD1, FIELD2)
    SELECT DISTINCT FIELD1, FIELD2
    FROM ANOTHER_TABLE A
    WHERE FIELD2 = '1' AND
          NOT EXISTS (SELECT 1
                      FROM NEW_TABLE N
                      WHERE A.FIELD1 = N.FIELD1
                     );

Your code is updating data as well. For this, you can use MERGE or just delete the duplicate rows first:

DELETE FROM NEW_TABLE
    WHERE EXISTS (SELECT 1
                  FROM ANOTHER_TABLE A
                  WHERE A.FIELD1 = N.FIELD1
                 );

And then run the above query.