CODEWITHSUNDEEP
oracle-databaseperformance
user1961622
user1961622

Reputation: 11

database update on large table oracle taking huge time

I want to copy one column data into another column in a large table containing 10 millions records. I am using sys refcursor to copy data from one column into another column. It will taking more than 30 min to copy the data. I am using ORACLE 11gR2.

Is there any others alternative to do the same. Below is the scripts 

CREATE OR REPLACE PROCEDURE tblCursor(org_mig OUT SYS_REFCURSOR)
IS
  BEGIN
    OPEN org_mig FOR
    select id from tbl;
  END;
/
DECLARE
  org_mig SYS_REFCURSOR;
  t_id organization.id%TYPE;
  loop_var number(10);
  commit_interval number(10);
BEGIN
  loop_var :=1;
  commit_interval:=10000;
  tblCursor(org_mig);

  LOOP
    FETCH org_mig INTO t_id;
    EXIT WHEN org_mig%NOTFOUND;
    update tbl set col1=col2 where id=t_id;
    IF mod(loop_var,commit_interval)=0 THEN
      Commit;
    End if;
    loop_var :=loop_var+1;
  END LOOP;
  Commit;
  CLOSE org_mig;
END;
/

Upvotes: 0

Views: 287

Answers (2)

user1961622
user1961622

Reputation: 11

Still it has been taken long time to update.


I am trying with different one but getting error. 

-----------------------------------
Error starting at line : 43 in command -
SELECT *
FROM  TABLE(test_parallel_update(CURSOR(SELECT * FROM organization)))
Error report -
SQL Error: ORA-12801: error signaled in parallel query server P003
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "QA249.TEST_PARALLEL_UPDATE", line 21
12801. 00000 -  "error signaled in parallel query server %s"
*Cause:    A parallel query server reached an exception condition.
*Action:   Check the following error message for the cause, and consult
           your error manual for the appropriate action.
*Comment:  This error can be turned off with event 10397, in which
           case the server's actual error is signaled instead.

---------------------------
Here is the script:




CREATE OR REPLACE TYPE test_num_arr AS TABLE OF NUMBER;
CREATE OR REPLACE FUNCTION test_parallel_update (
    test_cur IN SYS_REFCURSOR
) 
RETURN test_num_arr
PARALLEL_ENABLE (PARTITION test_cur BY ANY)
PIPELINED
IS
    PRAGMA AUTONOMOUS_TRANSACTION;

    test_rec organization%ROWTYPE;
    TYPE num_tab_t IS TABLE OF NUMBER(10,0);
    TYPE vc2_tab_t IS TABLE OF number(1,0);

    id NUM_TAB_T;
    org_type_old NUM_TAB_T;
    IS_DELETED_old VC2_TAB_T;

    cnt INTEGER := 0;
BEGIN
    LOOP
        FETCH test_cur BULK COLLECT INTO id, org_type_old, IS_DELETED_old LIMIT 1000;
        EXIT WHEN id.COUNT() = 0;

        FORALL i IN id.FIRST .. id.LAST
            UPDATE organization 
            SET    org_type = org_type_old(i)
            ,      IS_DELETED = IS_DELETED_old(i)
            WHERE  id = id(i);

        cnt := cnt + id.COUNT;
    END LOOP;

        CLOSE test_cur;

    COMMIT;
    PIPE ROW(cnt);
    RETURN;
END;
/
show error;
---- To Execute ----
SELECT *
FROM  TABLE(test_parallel_update(CURSOR(SELECT * FROM organization))); 



Note: 
Table
organization
(
id number(10,0),
org_type number(10,0),
org_type_old number(10,0),
IS_DELETED number(1,0),
IS_DELETED_OLD number(1,0)
);

where id is a primary key, Now I want copy org_type_old and IS_DELETED_OLD into org_type and IS_DELETED respectively.

Upvotes: 0

APC
APC

Reputation: 146239

You're doing this for every row in tbl, right? If so, you should just do this:

update tbl
set col1 = col2
/

Updating ten million rows will take some time, but a set operation will be way faster than the Row By Agonizing Row approach you've implemented. Plus, batching up your commits like that is bad practice. Not only does it slow things down, that approach can lead to ORA-01555: Snapshot too old exceptions. Find out more.

Upvotes: 1

Related Questions