Home » SQL & PL/SQL » SQL & PL/SQL » Data migration from One table to another
Data migration from One table to another [message #187852] Wed, 16 August 2006 00:41 Go to next message
amkotz
Messages: 72
Registered: May 2005
Location: Bangalore
Member
Hello All,

I have to migrate the data from one table to another.
Both the tables have around millions of rows.
I have written one migration script , but it takes around 1 full day to completly run and commit.

The Table Structure looks like:

Table_A
--------------------
HPS_ID Number
METH_NAME VARCHAR2(100)
DET_BL_INDEX VARCHAR2(100)
ARRAY_INDEX VARCHAR2(100)
VARIANTE VARCHAR2(10)
KOMMENTAR VARCHAR2(2000)
GL_B1 Number
GL_B2 Number
GL_B3 Number
GL_A1 Number
GL_A2 Number
GL_A3 Number
Condition Varchar2(100)

Table_B
--------------------
HPS_ID Number
NAME VARCHAR2(100)
TESTUNIT VARCHAR2(100)
TESTPIN1 VARCHAR2(100)
TESTBEZUG1 VARCHAR2(100)
TESTPIN2 VARCHAR2(100)
GL_B1 Number
GL_B2 Number
GL_B3 Number
GL_A1 Number
GL_A2 Number
GL_A3 Number
Condition Varchar2(100)

Table_C
---------------------
HPS_ID NUMBER
STATUS NUMBER
PLATFORM VARCHAR2(1)


No. of rows in table Table_A - 25,03,717
No. of rows in table Table_B - 40,82,108

What Migration:
Auctually there is a transfer of columns from Table_B to Table_A. ( around 5 columns )

There is a update of data on columns from Table_A to table Table_B . The Table_C is used only
for performing the migration one by one, as the HPS_ID is the primary key on the Table_C,
and it is foreign key on Table_A and Table_B.
It is just to perform the migration in steps.

The Script looks something like this:

/****************************************************************************************/
SET SERVEROUTPUT ON;
SET LINESIZE 1000;
SET FEEDBACK OFF;
DECLARE

CURSOR Cur_HpsId
IS
SELECT Hps_Id,
Name
FROM Table_C;

/*Cursor to check Weather any differences are there after
data Migration. ('Table_A' MINUS 'Table_B').
*/
CURSOR Cur_Diff1(
p_Hps_Id IN NUMBER
)
IS
SELECT COUNT(1)
FROM (
SELECT A.Meth_Name, A.Condition, A.GL_B1,
A.GL_B2, A.GL_B3, A.GL_A1,
A.GL_A2, A.GL_A3
FROM Table_A A
WHERE A.HPS_ID = P_Hps_Id
MINUS
SELECT B.Name, B.Condition, B.GL_B1,
B.GL_B2, B.GL_B3, B.GL_A1,
B.GL_A2, B.GL_A3
FROM Table_B B
WHERE B.HPS_ID = P_Hps_Id);

/*Cursor to check Whether any differences are there after
data Migration. ('Table_B' MINUS 'Table_A').
*/
CURSOR Cur_Diff2(
p_Hps_Id IN NUMBER
)
IS
SELECT COUNT(1)
FROM (
SELECT A.Meth_Name, A.Condition, A.GL_B1,
A.GL_B2, A.GL_B3, A.GL_A1,
A.GL_A2, A.GL_A3
FROM Table_A A
WHERE A.HPS_ID = P_Hps_Id
MINUS
SELECT B.Name, B.Condition, B.GL_B1,
B.GL_B2, B.GL_B3, B.GL_A1,
B.GL_A2, B.GL_A3
FROM Table_B B
WHERE B.HPS_ID = P_Hps_Id);

v_LoopCnt NUMBER := 0;
v_DiffCnt1 NUMBER := 0;
v_DiffCnt2 NUMBER := 0;
BEGIN
DBMS_OUTPUT.ENABLE(10000000000);
FOR I IN Cur_HpsId
LOOP

v_LoopCnt := v_LoopCnt + 1;

UPDATE Table_A A
SET (A.Condition,
A.GL_B1,
A.GL_B2,
A.GL_B3,
A.GL_A1,
A.GL_A2,
A.GL_A3)
=
(SELECT B.Condition,
B.GL_B1,
B.GL_B2,
B.GL_B3,
B.GL_A1,
B.GL_A2,
B.GL_A3
FROM Table_B B
WHERE B.Hps_Id = A.Hps_Id
AND B.Name = A.Meth_Name)
WHERE A.Hps_Id = I.Hps_Id;

v_DiffCnt1 := 0;
OPEN Cur_Diff1(I.Hps_Id);
FETCH Cur_Diff1 INTO v_DiffCnt1;
CLOSE Cur_Diff1;

v_DiffCnt2 := 0;
OPEN Cur_Diff2(I.Hps_Id);
FETCH Cur_Diff2 INTO v_DiffCnt2;
CLOSE Cur_Diff2;

DBMS_OUTPUT.PUT_LINE('Differences after modification ( Table_A MINUS Table_B ) :='||v_DiffCnt1);
DBMS_OUTPUT.PUT_LINE('Differences after modification ( Table_B MINUS Table_A ) :='||v_DiffCnt2);

COMMIT;

END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000,'Error:'||SQLCODE||'-'||SQLERRM);
END;
/
/****************************************************************************************/

Can anybody please help me by looking at this query , in any way that i can tune this query.
Which part of the query i can check to tune this procedure.

Thanks and Regards,
Amkotz

[Updated on: Wed, 16 August 2006 00:50]

Report message to a moderator

Re: Data migration from One table to another [message #188119 is a reply to message #187852] Thu, 17 August 2006 03:26 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
Is there any indexes on tables Table_A and Table_B ?

You should create indexes on table_a (Hps_Id) and table_b(Hps_Id,Name).
Re: Data migration from One table to another [message #188131 is a reply to message #188119] Thu, 17 August 2006 04:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is there a reason you can't do it as one big update.
Something like

UPDATE Table_A A
SET (A.Condition,A.GL_B1,A.GL_B2,A.GL_B3,A.GL_A1,A.GL_A2,A.GL_A3) 
= 
(SELECT  B.Condition,B.GL_B1,B.GL_B2,B.GL_B3,B.GL_A1,B.GL_A2,B.GL_A3
FROM Table_B B
WHERE B.Hps_Id = A.Hps_Id
AND B.Name = A.Meth_Name)
WHERE A.Hps_Id in (SELECT HPS_Id from Table_c); 


If you can't do that, then get that commit out of the loop, and look at doing the reporting afterwards as 2 big queries, rather than a 2 queries per row.

Also, the two cursors Curr_diff1 and Curr_diff2 are identical. I suspect that you want to switch the tables round in Curr_diff2
Re: Data migration from One table to another [message #188161 is a reply to message #188131] Thu, 17 August 2006 05:55 Go to previous message
vino4ever
Messages: 11
Registered: July 2006
Location: Chennai
Junior Member
Hi,

Try the following changes,

1) Change the cursor such that it fetches the Hps_ID from Table_A, as given below
SELECT     hps_id
      FROM table_a a
     WHERE EXISTS (SELECT 1
                     FROM table_b b
                    WHERE b.hps_id = a.hps_id)
FOR UPDATE


2) Change your update statement as given below,

UPDATE Table_A A
SET (A.Condition,A.GL_B1,A.GL_B2,A.GL_B3,A.GL_A1,A.GL_A2,A.GL_A3) 
= 
(SELECT  B.Condition,B.GL_B1,B.GL_B2,B.GL_B3,B.GL_A1,B.GL_A2,B.GL_A3
FROM Table_B B
WHERE B.Hps_Id = A.Hps_Id
AND B.Name = A.Meth_Name)
WHERE CURRENT OF <CURSOR_NAME>


Thanks
Vinod
Previous Topic: RAD tools for PL/SQL web development application
Next Topic: Reports in SQL
Goto Forum:
  


Current Time: Sat Dec 10 22:45:43 CST 2016

Total time taken to generate the page: 0.08967 seconds