>Subject: Join Across a Link
>From: Jeffrey Michael Gerber <jeffrey_gerber_at_mail.amsinc.com>
>Date: 2/2/98 6:49PM GMT
>Message-id: <34D61525.39E5F713_at_mail.amsinc.com>
>
>We have the following piece of SQL (actually, it's longer, but this is
>the relevant part):
> WHERE ( "A"."NUM" = "C"."ID") AND
> ( "A"."NUM" = 12)
>
>
>The "A" table is in the current database while the "C" table is in
>another database accessed via link. Is it true that a join across a
>link will bring back ALL rows and then do the join (i.e., we'll get all
>rows in "C" and then it'll compare it to the row in "A" which NUM =
>12)? Our client has suggested the following...
> WHERE C.NUM = 12 AND A.ID = 12
>
>That is, to get rid of the join. Does this make sense? Are we
>defeating some Oracle optimization? Is this actually equivalent (i.e.,
>will we always get the same result set from both statements)?
>
>Thanks!
>
>-Jeff
>
>
>
>
>
>
>
We experiencd the same problem where I work a couple of years ago. The ORACLE
documentation says that this is not the case if you are using COST based
optimisation - we were using RULE - but I've never tested it out. The new
query should be okay but to be doubly safe you could write it as
WHERE C.NUM = 12 AND A.ID = 12
AND C.NUM = A.ID
HTH
Rob
Received on Mon Feb 02 1998 - 00:00:00 CST
