OraFAQ Forum: Performance Tuning » How to tune a update statement

Home » RDBMS Server » Performance Tuning » How to tune a update statement (Oracle 10g, Solaris 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to tune a update statement [message #431904]

Thu, 19 November 2009 23:50

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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;

Report message to a moderator

Re: How to tune a update statement [message #431913 is a reply to message #431905]

Fri, 20 November 2009 00:51

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?

Report message to a moderator

Re: How to tune a update statement [message #431915 is a reply to message #431913]

Fri, 20 November 2009 00:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>any clues?

This is a FAQ.

Report message to a moderator

Re: How to tune a update statement [message #431971 is a reply to message #431904]

Fri, 20 November 2009 04:53

trantuananh24hg
Messages: 744
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.

Report message to a moderator

Re: How to tune a update statement [message #432127 is a reply to message #431904]

Sat, 21 November 2009 09:26

wakula
Messages: 150
Registered: February 2008
Location: Poland

Senior Member

What about CODE tag and formatting?
Posting guidelines....

Report message to a moderator

Re: How to tune a update statement [message #432186 is a reply to message #431904]

Sun, 22 November 2009 17:36

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.

Report message to a moderator

Re: How to tune a update statement [message #432187 is a reply to message #432186]

Sun, 22 November 2009 20:59

rleishman
Messages: 3728
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

Report message to a moderator