Home » SQL & PL/SQL » SQL & PL/SQL » Optimization - reuse of a set (Oracle 10i)
Optimization - reuse of a set [message #447840] Thu, 18 March 2010 03:52 Go to next message
yav0r
Messages: 1
Registered: March 2010
Junior Member
Hello,

I have the following situation. There are two selects in the which look like this

UPDATE TABLE_B B
SET (
target
) = (
SELECT
SUM( MyFunction (a.x) )
FROM
TRANCHE Table_A a
WHERE
-- several conditions like
a.z = b.z
);


UPDATE TABLE_B B
SET (
target2
) = (
SELECT
SUM( MyFunction (a.x) )
FROM
TRANCHE Table_A a
WHERE
-- several conditions like
a.z = b.z
a.col2 > b.col3
);

DB: Oracle 10i

MyFunction is relatively expensive and the second select works on a subset of the data considered in the first one (because of a.col2 > b.col3). For this reason I am looking for a way to do the job in only one update statement and compute MyFunction (a.x) only once per row.
Does someone have an idea if it is possible to do this without an extra table?

Thanks in advance!
Yav
Re: Optimization - reuse of a set [message #447854 is a reply to message #447840] Thu, 18 March 2010 04:35 Go to previous message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Record the result of SELECT into a temporary table with 2 columns one for the first update and the other one for the second update, then use this temporary table.

Regards
Michel
Previous Topic: leftouter join for below scenario
Next Topic: Compilation error with simple ELSIF
Goto Forum:
  


Current Time: Mon Sep 26 16:16:34 CDT 2016

Total time taken to generate the page: 0.06928 seconds