Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query - Update performance
SQL Query - Update performance [message #203053] Mon, 13 November 2006 13:40 Go to next message
Suresh.N
Messages: 41
Registered: March 2000
Member
Hi,

I have 2 tables T1 and T2.
Table T1 has 5 million records.
Table T2 has 20000(20 thousand) records.

I want to update table T1 by joining with table T2.
I used the following query. In both tables column C1 has been indexed.

The query below is taking more time to perform the update.
I tried to update by using loop.

Is there any other way to improve the performance?

UPDATE T1
SET T1.C3=
(SELECT T1.C2 * T2.C2
FROM T2
WHERE T1.C1=T2.C1)

Thanks in advance.

Regards
Suresh
Re: SQL Query - Update performance [message #203061 is a reply to message #203053] Mon, 13 November 2006 14:46 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Your current query will update every row in T1 (5 million) regardless of whether there is a matching row in T2.

update t1
   set c3 = (select t1.c2 * t2.c2
               from t2
              where t2.c1 = t1.c1)
 where c1 in (select c1
                from t2);


Does the execution plan for this query use the T1.C1 index (and the T2.C1 index)?
Re: SQL Query - Update performance [message #203071 is a reply to message #203061] Mon, 13 November 2006 15:36 Go to previous messageGo to next message
Suresh.N
Messages: 41
Registered: March 2000
Member
Todd,

Thanks for the Reply.

T2 has all values in table T1(5 million).
All the T1.C1 Values are available in T2.C1. (Like 1 to many).
So I have not used the where class in the main Update query as you mentioned.


The execution Plan did not use the T1.C1 index. But it used the T2.C1 index.

The query should update all 5 million records.

Thanks in Advance.

Regards
Suresh

Re: SQL Query - Update performance [message #203082 is a reply to message #203071] Mon, 13 November 2006 16:52 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
OK, if you have to update all 5 million rows and the lookup in T2 is using the correct index, then the query you have is optimal.
Re: SQL Query - Update performance [message #203088 is a reply to message #203082] Mon, 13 November 2006 19:24 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
UPDATE (
  SELECT T1.C3 AS OLD_C3
  ,      T1.C2 * T2.C2 AS NEW_C3
  FROM   T1 
  JOIN   T2 ON T1.C1=T2.C1
)
SET OLD_C3 = NEW_C3

You will need a unique/PK constraint on t2.c1

See here for more info.

You could also do it with a MERGE statement.

Ross Leishman
Previous Topic: Query a view
Next Topic: what method is used in decrementing of cnt from day 13
Goto Forum:
  


Current Time: Sat Dec 10 01:29:11 CST 2016

Total time taken to generate the page: 0.09955 seconds