如何优化用于从表复制数据的oracle过程？

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).

我的oracle数据库中有两个不同的表。我只复制我的第一个表(ANOTHER_TABLE)的部分数据,只选择一些列并过滤行,换成新的(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?

我尝试使用以下过程,使用游标,但我注册了很长的执行时间。我该如何优化这个oracle sql程序?有可能的?

For information, the ANOTHER_TABLE contains about 500k records.

有关信息,ANOTHER_TABLE包含大约500k记录。

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

1 个解决方案

#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:

通常,最快的方法是使用单个查询,而不是使用游标。如果要在NEW_TABLE中插入新的不同值(如标题所示),则可以使用单个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:

您的代码也在更新数据。为此,您可以使用MERGE或只删除重复的行:

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

And then run the above query.

然后运行上面的查询。

1 个解决方案

#1

更多相关文章

随机推荐