Home » RDBMS Server » Performance Tuning » How to tune a update statement (Oracle 10g, Solaris 10)
icon8.gif  How to tune a update statement [message #431904] Thu, 19 November 2009 23:50 Go to next message
lucas4394
Messages: 24
Registered: October 2005
Junior Member
All,

We have a simple update statement take more than 8 hours to
update 7-8 millions records into a 36-million records table:

UPDATE myTarget partition (xyz) mt
SET (col1, col2, col3, col4, col5) =
(SELECT c1, c2, c3, c4, c5
FROM sourceTable
WHERE id = mt.id);


myTarget: 7 partitions
10 foreign key constraints
55 millions records +
id is the primary key

sourceTable: staging table
non-partitioned
7-9 millions records
id is indexed

How can I rewrite the query to run it faster?

Thanks

[Updated on: Thu, 19 November 2009 23:54]

Report message to a moderator

Re: How to tune a update statement [message #431905 is a reply to message #431904] Thu, 19 November 2009 23:53 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>How can I rewrite the query to run it faster?
EXACTLY where is the time being spent?
If you do not know where it is slow, you can not identify what needs to be made faster.

ALTER SESSION SET SQL_TRACE=TRUE;
Re: How to tune a update statement [message #431913 is a reply to message #431905] Fri, 20 November 2009 00:51 Go to previous messageGo to next message
lucas4394
Messages: 24
Registered: October 2005
Junior Member
I tried the following

UPDATE 
(SELECT mt.col1 col1, st.c1 c1, mt.col2 col2, st.c2 c2 ...
  FROM myTarget mt, sourceTable st
 WHERE mt.id = st.id)
SET col1 = c1,
    col2 = c2, 
   ...


However, I am getting the following:
ora-01779 cannot modify a column which maps to a non key preserved value

any clues?
Re: How to tune a update statement [message #431915 is a reply to message #431913] Fri, 20 November 2009 00:56 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>any clues?

This is a FAQ.

Re: How to tune a update statement [message #431971 is a reply to message #431904] Fri, 20 November 2009 04:53 Go to previous messageGo to next message
trantuananh24hg
Messages: 664
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
lucas4394 wrote on Fri, 20 November 2009 12:50
All,

We have a simple update statement take more than 8 hours to
update 7-8 millions records into a 36-million records table:


Swan, said lucas as his update statement need about 8 hours to complete.
@lucas: Why did you not post the statistics & statement's plan here? (tkprof or set autotrace traceonly..). And you'd better post the table and indexes's structure.
Re: How to tune a update statement [message #432127 is a reply to message #431904] Sat, 21 November 2009 09:26 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
What about CODE tag and formatting?
Posting guidelines....
Re: How to tune a update statement [message #432186 is a reply to message #431904] Sun, 22 November 2009 17:36 Go to previous messageGo to next message
oracleperm
Messages: 2
Registered: November 2009
Location: California
Junior Member
Tune the query, generate execution plan, or 10046 trace to see where the time is being spent...or adding parallel.

Re: How to tune a update statement [message #432187 is a reply to message #432186] Sun, 22 November 2009 20:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If it is updating many rows, it is slow because the sub-query is nested. See here for a detailed explanation.

Convert it to a MERGE statement as suggested in that article.

Ross Leishman
Re: How to tune a update statement [message #432188 is a reply to message #432187] Sun, 22 November 2009 21:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Actually, a CTAS will be TONS better if you are updating every row.

Ross Leishman
Re: How to tune a update statement [message #432218 is a reply to message #431913] Mon, 23 November 2009 01:10 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Do you have an unique index on id column of sourceTable?

Previous Topic: how to enable trace for other sessions?
Next Topic: Performance of a Query
Goto Forum:
  


Current Time: Thu Sep 29 14:13:23 CDT 2016

Total time taken to generate the page: 0.08465 seconds