RE: outer join between parent-child, not null and FK constraints
From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Thu, 24 Jan 2013 17:57:50 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF01FEE076_at_WIN02.hotsos.com>
The CBO is cleaver and is able to do many things. Maybe in a release or two it will do something about this too. But does it matter for one thing? Is the performance any better without the outer join? Redundant or not, does it really help the query to not do the outer join? I don't know. It may be possible that the out join is just as efficient, so why bother getting rid of it?
Date: Thu, 24 Jan 2013 17:57:50 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF01FEE076_at_WIN02.hotsos.com>
The CBO is cleaver and is able to do many things. Maybe in a release or two it will do something about this too. But does it matter for one thing? Is the performance any better without the outer join? Redundant or not, does it really help the query to not do the outer join? I don't know. It may be possible that the out join is just as efficient, so why bother getting rid of it?
- Ric
From: amonte [mailto:ax.mount_at_gmail.com]
Sent: Thursday, January 24, 2013 6:16 PM
To: Ric Van Dyke
Cc: Oracle-L Group
Subject: Re: outer join between parent-child, not null and FK
constraints
Hi Rich
your query returns all rows in dept
why am I thinking oracle should be smart enough to detect this? it is because CBO can now do join elimination and the example I posted it is not difficult to implement in CBO no?
Thanks!
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 25 2013 - 00:57:50 CET