RE: Are these suppose to be equivalent?
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 28 Sep 2004 11:39:08 -0700
Your subquery is a correlated subquery so it will be executed once for every
distinct combination of values of the correlating columns, not just once and
the value used for all rows of the outer table.
So this is not another incident of subquery madness but it is clear that Oracle
must correlated the result of the subquery to the correct rows of the outer
table or else the result would be wrong. Older versions of the optimizer would
simply process the outer table row by row and execute the subquery for each
qualifying row. Newer, smarter versions recognize the possible repetition and
execute the subquery only for distinct values of the correlating columns.
Quoting Stephen.Lee@xxxxxxxx:
>
> I think there is a 1:1 relation. On the surface, based on what I know about
> SQL, it would seem that if the subquery returns one value, then all columns
> of planb_location would be set to that value; if the subquery returns
> multiple values, the result should be an error(??). But there is no error.
> It works, and the updates correspond correctly. So clearly, Oracle is
> keeping the rows that match up "inside" the subquery properly matched up
> "outside" the subquery. And now we are back to the whole issue of whether
> there is an official inside and outside of the subquery. That is: Is the
> update with the subquery SUPPOSED to work, or does it just happen to work
> because of some subquery magic applied by Oracle.
>
> >-----Original Message-----
> >It would appear that statement #2 should update a subset of the data
> >that statement #1 is updating.
> >
> >One exception would be if there is a 1:1 relation between location
> >and planb_location.
> --
> //www.freelists.org/webpage/oracle-l
>
--
regards
Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
