Home » SQL & PL/SQL » SQL & PL/SQL » Update statement
Update statement [message #193190] Fri, 15 September 2006 04:16 Go to next message
Messages: 7
Registered: September 2006
Junior Member
I have one table contains about 15 millions records with data in several years. Each year contains about 3 millions rows. Well, i must update a field in this table. My update query is:

update my_table1 set table1_column1 = (select table2_column1 from my_table2 where table2_column2 = my_value2) where table1_column2 = my_value2 and year = '2005';

The above-mentioned my_table2 contains about 100,000,000 records.
I must execute this update query several times and i see that it is very very slow. I really want to speed it up. I'm in a divided mind as to what to do.
Plz show me your suggestion. Thanks in advance.
Re: Update statement [message #193201 is a reply to message #193190] Fri, 15 September 2006 04:50 Go to previous messageGo to next message
Messages: 137
Registered: September 2006
Senior Member

Try This

update (
select table1_column1 , table2_column1
from my_table1 , my_table2
where table1_column2 = my_value2 and year = '2005' and
table2_column2 = my_value2 )
set table1_column1= table2_column1

Re: Update statement [message #193375 is a reply to message #193201] Fri, 15 September 2006 22:40 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you are updating more than 10% of the rows in the table, it will almost certainly be faster to rebuild the table using CTAS (Create Table As Select). CTAS is also often faster when as little as 2% or 3% table needs to be updated - it depends on the structure, indexes, and columns updated.

Ross Leishman
Previous Topic: TRUNCATE TABLE
Next Topic: To retrieve the numbers which are not presented in the table
Goto Forum:

Current Time: Mon Jul 24 17:59:32 CDT 2017

Total time taken to generate the page: 0.09265 seconds