OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Problem in Update (ORACLE 10g)

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

Problem in Update [message #554260]

Fri, 11 May 2012 12:15

priyankt
Messages: 10
Registered: February 2011

Junior Member

Hello ,

I am trying to update currency column of one table using the currency column of other table using the following sql code.

update ODS.SO_ITEM OSI
set CURRENCY__CODE=(select currency__code from sa_sales.SO_ITEM SSI where SSI.ID=OSI.ID)

This update is taking taking a lot of time and is never ending.

Please suggest should i create index on source table (SA_SALES.SO_ITEM) or on target table (ODS.SO_ITEM) ?

Regards,
Priyanka

Report message to a moderator

Re: Problem in Update [message #554261 is a reply to message #554260]

Fri, 11 May 2012 12:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>Please suggest should i create index on source table (SA_SALES.SO_ITEM) or on target table (ODS.SO_ITEM) ?
NO, neither

make INDEX for both SSI.ID & OSI.ID

Report message to a moderator

Re: Problem in Update [message #554266 is a reply to message #554260]

Fri, 11 May 2012 12:41

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

From your previous topics:

Michel Cadot wrote on Sun, 06 February 2011 19:24

Read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.
...

BlackSwan wrote on Thu, 03 February 2011 05:15

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
...

BlackSwan wrote on Thu, 03 February 2011 05:27

Please realize that we don't have your tables & we don't have your data.
Therefore we can't run, test or improve your posted SQL.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

And also, it is appreciated and fair to feedback to those who (try to) help you.

Regards
Michel

Report message to a moderator

Re: Problem in Update [message #554383 is a reply to message #554260]

Mon, 14 May 2012 01:11

arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates

Senior Member

is the size of source currency table is big, check for indexes and use explain plan to check the cost it takes to execute the query.

Report message to a moderator

Re: Problem in Update [message #554482 is a reply to message #554383]

Mon, 14 May 2012 14:33

msol25
Messages: 396
Registered: June 2011

Senior Member

Hi,

Please follow following steps :

Step 1:


explain plan for
update ODS.SO_ITEM OSI
set    CURRENCY__CODE =
( 
   select currency__code 
   from   sa_sales.SO_ITEM SSI 
   where  SSI.ID=OSI.ID
);

Step 2:


select *
from   table(dbms_xplan.display);

Step 3:

then check if any table is having more records and you are selecting 10-30% records from that table then create index on that column

Report message to a moderator

Re: Problem in Update [message #554506 is a reply to message #554482]

Tue, 15 May 2012 00:47

arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates

Senior Member

I tested this case sol25 and in case if it shows more than 40% , do i need to create a unique index or normal index and what is the reason behind that , can you explain me the methods to find tune sql queries as i have a huge tables and based on that i am running report and it takes hours to generate.

Report message to a moderator

Re: Problem in Update [message #554873 is a reply to message #554506]

Fri, 18 May 2012 03:46

priyankt
Messages: 10
Registered: February 2011

Junior Member

Hello All, Thanks a lot for reverting back and giving your valuable suggestions.

My problem resolved after creating indexes on SSI.ID & OSI.ID as suggested by blackswan

Report message to a moderator

Re: Problem in Update [message #554874 is a reply to message #554261]

Fri, 18 May 2012 03:46

priyankt
Messages: 10
Registered: February 2011

Junior Member

Thanks a lot.. your suggestion helped me in resolving the issue.

Report message to a moderator

Previous Topic:	query slow in application, fast on SQL*Plus executing
Next Topic:	Query not hitting index on date column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 13 07:42:10 CDT 2025