Re: outer join between parent-child, not null and FK constraints

From: amonte <ax.mount_at_gmail.com>
Date: Fri, 25 Jan 2013 07:56:34 +0100
Message-ID: <CABV7K9uZBYhSpEZ60+WGr55ctjmBU4v2hGbdyDrX7G9FecefFw_at_mail.gmail.com>

Hi Ric
I am not saying performance is better or outer join is bad nor it's not efficient.

I am asking that if the CBO can do thing such as join elimination using information from constraints why it doesnt do outer join elimination in tuis case? This looks a rule to me, in a parent child relationship a parent must have child, a FK must have parent, if I make FK NOT NULL Then all child table MUST have parent (cannot have null in FK) so if all this meet if someone writes an outer join if we applies the rule we know that outer join is redundant, correct? Isnt this a simple rule for CBO?

I just want to prove that this is a rule. Do you think this is a rule?

Thanks!

Alex

2013/1/25 Ric Van Dyke <ric.van.dyke_at_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****
>
>
>

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

Received on Fri Jan 25 2013 - 07:56:34 CET