Home » SQL & PL/SQL » SQL & PL/SQL » How to update a table with millions of rows
How to update a table with millions of rows [message #199507] Tue, 24 October 2006 18:50 Go to next message
dwandbi
Messages: 2
Registered: October 2006
Junior Member
Hi,
I have a table, T1 say with x columns and I want to add a new column. Table has got millions of rows. I want to look up value for this new column from another table, T2. Two tables have got a column in matching. How to achieve this without losing any data from the first table(T1). Tables have no indexes.

I appreciate your help.

thanks,
dwandbi
Re: How to update a table with millions of rows [message #199510 is a reply to message #199507] Tue, 24 October 2006 21:40 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The fastest way is to rebuild the table:
CREATE TABLE new_table AS
SELECT o.col1, o.col2, o1.col3
FROM old_table o
JOIN other_table o1 ON o.col1 = o1.col1;

DROP TABLE old_table;
RENAME new_table TO old_table;


Ross Leishman
Re: How to update a table with millions of rows [message #199609 is a reply to message #199507] Wed, 25 October 2006 04:20 Go to previous messageGo to next message
dwandbi
Messages: 2
Registered: October 2006
Junior Member
Hi,
Thanks for reply. But I have one doubt. In this way of creating a new table by taking columns from table, O and another column from table O1 by simple join will be resulting in cartesian product or not?

I just want a final table with same number of rows in table, O.
Again, there are no indexes on the table. There are some duplicate rows in table, O. Also what to do if a matching value for col3 from O1 is not found for that joining column. Can I use nvl to handle nulls?

Thanks for your help.
dwandbi

Re: How to update a table with millions of rows [message #199620 is a reply to message #199507] Wed, 25 October 2006 04:47 Go to previous messageGo to next message
mahendramahendra
Messages: 6
Registered: October 2006
Junior Member

you can do outerjoin so that you can have all rows from "old_table" table

or

alter the old table to add new column

and then do something like

UPDATE old_table o
SET new_col = (
SELECT new_value
FROM another_table a
WHERE a.col1 = o.col1 );

In the first method if you have any index / constraints / triggers you will loose, but the second method keeps everything.

[Updated on: Wed, 25 October 2006 04:48]

Report message to a moderator

Re: How to update a table with millions of rows [message #199628 is a reply to message #199620] Wed, 25 October 2006 09:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Updating all the rows will be painful. We need to consider the row migration and cost of updating indexes.
Re: How to update a table with millions of rows [message #199679 is a reply to message #199628] Wed, 25 October 2006 15:45 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
UPDATE old_table o
SET new_col = (
SELECT new_value
FROM another_table a
WHERE a.col1 = o.col1 );

defnly you don't want to do this on a table which contains million rows.

Instead try something like this

Update (select <query>)
set new_col = col_value;
Previous Topic: Information Hiding in pl/sql
Next Topic: SQL Look Back
Goto Forum:
  


Current Time: Wed Dec 07 04:45:01 CST 2016

Total time taken to generate the page: 0.08729 seconds