MINUS operator - How it works.!!??

[Newbie]

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.

TIA

[Anna C. Dent]

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

[Mark D Powell]

"Anna C. Dent" <an******@hotmail.com> wrote in message news:<sOeub.23702$Q64.11116@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 --