| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL. Correlated UPDATE problem.
A copy of this was sent to Leonard Will <L.Will_at_Willpower.demon.co.uk>
(if that email address didn't require changing)
On Mon, 14 Feb 2000 11:44:59 +0000, you wrote:
>In article <87rats$92h$1_at_serv1.iunet.it>, on Wed, 9 Feb 2000 at 10:10:07,
>Marcelllo Cecci <URL: mailto:mcecci_at_seldat.it> writes
>>try this
>>statement:
>>
>>UPDATE
>> customers
>>SET
>> cusname = (SELECT Cname FROM Customers_newinfo
>> WHERE Customers_newinfo.Cno = customers.Cusno)
>> and exist
>> (SELECT * FROM Customers_newinfo
>> WHERE Customers_newinfo.Cno = customers.Cusno)
>>;
>
>Some follow-up questions about this:
>
>1. Does Oracle optimise this sort of question where you need to update a
>field by the results of a complex SELECT statement only if that result is
>not null? I.e. if you have to repeat the select statement twice, as above, is
>it executed twice?
>
it's done 2x in the above. If customers_newinfo.cno has a unique index (it has should for both performance and correctness, cno must be unique for the above to work else the single row subquery would return >1 row) then it can be written as:
update
( select a.cname NEW_CNAME, b.cusname OLD_CNAME
from customers_newinfo a, customers b
where a.cno = b.cusno )
This opens the optimizer to more choices then the first query as well. If A is much smaller then B and you've analyzed, then the optimizer may very well use a full scan on A to drive into B. If A and B are about the same size -- it'll pick one or the other.
The first query would almost certainly full scan the customers table due to the semantics of the where exists and the correlated subquery in the update.
>2. If the statement has to be repeated, is it better to select * in the
>second occurrence, as above, or to select Cname to make the
>statements identical, or to select a dummy text value such as 'x' to save
>the system from having to retrieve any data?
>
It won't make a difference. If there is an index on customers_newinfo.cno, it will understand it only needs to do an index probe and not really get '*', or any set of columns. It would not access the table for the where exists regardless of whether you use *, NULL, 'X', etc...
>3. Is there a neat and easy way to put the results of the statement into a
>variable (host variable or PL/SQL) that can be used for the "exists" test?
>Is it worthwhile to do so in terms of speed of execution?
>
update the join -- that'll typically be faster and have more execution plans available for consideration.
>Leonard Will
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Feb 14 2000 - 07:09:58 CST
 |
 |