Home » SQL & PL/SQL » SQL & PL/SQL » Need to Update 20 million records of data (10g, Unix)
Need to Update 20 million records of data [message #376664] Thu, 18 December 2008 03:44 Go to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi,

I have a table T1 in database A and T2 in database B.
I need to update few columns in T2 from T1( both are in different databases). They have the same primary key. Please let me know how can efficiently can we do this.
Re: Need to Update 20 million records of data [message #376668 is a reply to message #376664] Thu, 18 December 2008 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database, is this real database or schema?

Regards
Michel
Re: Need to Update 20 million records of data [message #376677 is a reply to message #376664] Thu, 18 December 2008 04:29 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

They are two diffrenet databases on different servers.

One database is of Oracle 9i and other is of Oracle 10g.
Re: Need to Update 20 million records of data [message #376689 is a reply to message #376677] Thu, 18 December 2008 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy (in any way you want) B inside A database and does the update locally.

Regards
Michel
Re: Need to Update 20 million records of data [message #376696 is a reply to message #376689] Thu, 18 December 2008 05:22 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi Michel ,

Thanks For the Reply. I done the copy as u suggested. but now how can i proceed to update efficiently, as the source table is having around 20 million records.Any link or Approach to follow will be appreciated.
Re: Need to Update 20 million records of data [message #376700 is a reply to message #376664] Thu, 18 December 2008 05:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming the pk columns are indexed, just do something like this:
UPDATE table_2
SET    <columns> == (SELECT <columns>
                     FROM   table_1
                     WHERE  table_1.pk_column = table_2.pk_column)
WHERE  <list of conditions to identify the rows in Table 2 to be updated>
Re: Need to Update 20 million records of data [message #376703 is a reply to message #376664] Thu, 18 December 2008 05:39 Go to previous messageGo to next message
karthick_arp@yahoo.com
Messages: 6
Registered: November 2006
Location: India
Junior Member

I would try something like this.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330

Thanks,
Karthick.
Re: Need to Update 20 million records of data [message #376707 is a reply to message #376703] Thu, 18 December 2008 05:53 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi All,

Thnks For the reply.

If i proceed to Create a table as suggested by "Tom" in the above link.

When i am deleting the OLD TABLE what will happen to the Constriants ?? ( Foreign Key constriants).is it possible to delete the OLD TABLE ? since it will voilate the constriant.

If i delete by using CASCADE . what will happen to the child table ?.

Once if rename the NEW TABLE created as OLD TABLE will the constaints build automatically?..

Your's
Ravi

Re: Need to Update 20 million records of data [message #376715 is a reply to message #376707] Thu, 18 December 2008 06:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's the problem with that method - you need to disable all the constraints that refer to the table to be updated, and then after the table has been recreated, you need to recreate all the indexes, triggers, comments and constraints that were on the original table.

Re: Need to Update 20 million records of data [message #378673 is a reply to message #376715] Wed, 31 December 2008 04:48 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi All ,

Agian i am facing the prob with respect to update of the table.

SQL> select * from t1;

      COL1       COL2       COL3 D
---------- ---------- ---------- -
         1        100        200 A
         2        101        201 A
         3        102        202 A
         4        103        203 B
         5        104        204 B

SQL> select * from t2;

      COL1       COL2       COL3
---------- ---------- ----------
         1                
         2                
         3                
         4
         5


i am updating t2 with t1 contents as follows.

update t2 set (t2.col2,t2.col3) = ( select t1.col2,t1.col3
                                    from t1 
                                    where t1.col1 = t2.col1)


now the problem is t2 is having around 25 Million Records which wil match with t1. The update process will take so much time.
I thought of adding a flag to "t1" so i can do multiple updates.
But since it is in production i can't alter the table.

Any other way by which we can do the update fast ?...
Re: Need to Update 20 million records of data [message #378696 is a reply to message #378673] Wed, 31 December 2008 07:48 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Maybe
where col1 between 1 and 5000000

Previous Topic: to compare with sys_refcursor what could be the possible values other than %NOTFOUND??
Next Topic: Date function
Goto Forum:
  


Current Time: Wed Dec 07 14:51:26 CST 2016

Total time taken to generate the page: 0.07890 seconds