SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Sql Update from another table performance

513042Jul 7 2008 — edited Jul 7 2008

Hello I have the following query.

The subquery takes 2 mins if ran alone.
When I try to update off it , the process takes 30mins.
Is the subquery being executed for each row to update?

Please help

UPDATE TARGETS_TOLLFREE T3
SET MINS =( SELECT mins
FROM (SELECT ORIGINATION_LATA,ORIGINATION_OCN,
ROUND(SUM(INDURATION / 60) * 31* 1.3,0) AS MINS
FROM TSM_CDRS.CDRS_NACT T2
WHERE calldate BETWEEN TRUNC(SYSDATE) -1 AND TRUNC(SYSDATE)
AND REGEXP_LIKE('800;888;866;877;855',SUBSTR(DNIS,1,3))
GROUP BY ORIGINATION_LATA,ORIGINATION_OCN) T2

WHERE t3.lata = t2.ORIGINATION_lata
AND t3.ocn = t2.ORIGINATION_OCN)

Pinela

I would say so.

Since the subquery doesn't receive arguments from the t3 alias table, maybe the subquery should be run outside the update and the subquery's result should be stored in a variable to be used in the update....... if I'm reading it right..

561825

You will be better off read the following link. I assumed you are using oracle 10g and above.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#SQLRF01606

BTW, are you aware that you will be updating every single row in targets_tollfree ?

Regards

Raj

JustinCave

Do you really need to update every row of TARGETS_TOLLFREE as you are currently doing? Or do you only need to update those rows where the subquery returns a matching record? If the latter, you may want to slap a WHERE EXISTS (<<subquery>>) into your UPDATE statement.

Justin

513042

Thanks

The Merge method worked.

1 - 4

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 4 2008

Added on Jul 7 2008

4 comments

1,679 views

SQL & PL/SQL

Sql Update from another table performance

Comments

Post Details