Re: MINUS operator - How it works.!!??

"Anna C. Dent" <anacdent_at_hotmail.com> wrote in message news:<sOeub.23702$Q64.11116_at_fed1read03>...
> Newbie wrote:
> > Hi,
> > Could someone please tell how MINUS operator works for comparing and
> > giving non-matching records in Table1? Does MINUS operator compares
> > all records of Table1 with all records of Table2 to give differences?
> > Say, i've got 2 tables - no keys defined on them. No PK and UK. Now
> > MINUS operator will pick which columns for comparison? Having a PK/UK,
> > i see it as comparing these keys and susequently the rest of fields
> > for matcing key records, thereby reporting non-matching records from
> > Table1. Say, if both Table1 and Table2 have 300,00 records each. Now
> > how many comparisons would this result in?
> >
> > Someone please light a bulb on me... or direct me to some good links
> > that would clear my doubts on this.
>
> The answer is documented at http://tahiti.oracle.com
> go RTFM for enlightenment

I looked at the documentation related to minus and did not find the organization that useful in light that I believe the question is directed at determining how Oracle actually goes about solving a minus operation rather than what minus produces or how to use the minus operator. As such I do not think the answer is in the documentation though the documentation should clear up some of newbie's confusion related to his or her comments on PK.

Newbie, Logically when you use the minus operator you are asking for all the rows in A that are not in B. This can be thought of as an outer join of B to A so that all the rows in A are returned but you discard the matching rows leaving only the rows in A that have no match in B. I believe this is called an anti-join. The match is performed on all columns in the select list so the presence or absense of keys do not matter; duplicates are eliminated.

How Oracle goes about solving the query internally is not our concern in that Orcle could potentially change the internal methods employed at any time. You can run explain plan and trace files to see what Oracle is doing for a specific query.

HTH -- Mark D Powell -- Received on Wed Nov 19 2003 - 16:16:57 CET