Home » RDBMS Server » Performance Tuning » Problem in Update (ORACLE 10g)
Problem in Update [message #554260] Fri, 11 May 2012 12:15 Go to next message
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
Re: Problem in Update [message #554261 is a reply to message #554260] Fri, 11 May 2012 12:18 Go to previous messageGo to next message
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
Re: Problem in Update [message #554266 is a reply to message #554260] Fri, 11 May 2012 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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



Re: Problem in Update [message #554383 is a reply to message #554260] Mon, 14 May 2012 01:11 Go to previous messageGo to next message
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.
Re: Problem in Update [message #554482 is a reply to message #554383] Mon, 14 May 2012 14:33 Go to previous messageGo to next message
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

Re: Problem in Update [message #554506 is a reply to message #554482] Tue, 15 May 2012 00:47 Go to previous messageGo to next message
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.
Re: Problem in Update [message #554873 is a reply to message #554506] Fri, 18 May 2012 03:46 Go to previous messageGo to next message
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
icon14.gif  Re: Problem in Update [message #554874 is a reply to message #554261] Fri, 18 May 2012 03:46 Go to previous message
priyankt
Messages: 10
Registered: February 2011
Junior Member
Thanks a lot.. your suggestion helped me in resolving the issue.
Previous Topic: query slow in application, fast on SQL*Plus executing
Next Topic: Query not hitting index on date column
Goto Forum:
  


Current Time: Thu Mar 28 17:19:42 CDT 2024