RE: Need another pair(s) of eyes

Wolfgang,

They seem like perferctly valid transformations, do you have evidence (i.e. actual example) which shows the opposite? In a 2VL world these two pairs of expressions are equivalent. Maybe SQL's 3VL logic is throwing in some dirt? Allthough I don't see how/where.

Toon

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling Sent: donderdag 26 juli 2007 12:22
To: oracle-l
Subject: Need another pair(s) of eyes

Hi,

or probably more accurately a few more brains.

I think the following are equivalent:

do something (select, update, delete)
where < some predicates>
and exists ( select 'x' from tableA, tableB

   where < some regular predicates > and < some correlated predicates >    UNION ALL
   select 'x' from tableC, tableD (could be same as A and/or B)    where < some regular predicates > and < some correlated predicates >    )

and

do something (select, update, delete)
where < some predicates>
and ( exists ( select 'x' from tableA, tableB

   where < some regular predicates > and < some correlated predicates >
)

   OR exists (
   select 'x' from tableC, tableD (could be same as A and/or B)    where < some regular predicates > and < some correlated predicates >
)

   )

Likewise for not exists:

do something (select, update, delete)
where < some predicates>
and not exists ( select 'x' from tableA, tableB

   where < some regular predicates > and < some correlated predicates >    UNION ALL
   select 'x' from tableC, tableD (could be same as A and/or B)    where < some regular predicates > and < some correlated predicates >    )

vs.

do something (select, update, delete)
where < some predicates>
and not exists ( select 'x' from tableA, tableB

   where < some regular predicates > and < some correlated predicates >
)

   AND not exists (
   select 'x' from tableC, tableD (could be same as A and/or B)    where < some regular predicates > and < some correlated predicates >
)

Can someone think of some condition(s), examples where they would not be equivalent?

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
AIM: WBreitling

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l