Re: How do I outer join with 2 columns (inner view)?

On Sat, 31 Aug 2002 22:55:29 GMT, Don Chambers <dchamber_at_mindspring.com> wrote:

>I have a query with an inner view and need to perform an outer join on
>this inner view. I need to join on 2 columns to ensure I have the
>correct rows. Below is the query:
>
>SELECT mdc.id
> FROM part,
> (SELECT id, code, sos
> FROM tableB) mdc
>WHERE
> part.code = mdc.code(+)
> part.sos = mdc.sos(+)
>
>
>I'm not sure how to do an outer join with 2 columns. I'm also not
>sure if the fact that I have an inner view will change my where
>clause.
>
>The main table (part) has about 250,000 rows. The inner view produces
>about 2000 rows. When I run the query it takes a long time then I run
>out of temp space, which is 5 gig. I'm think I'm getting a cartesian
>result set.
>
>I'm running on Oralce 9i.
>
>Can anyone help me?
>
>Thanks,
> Don
>
>

Hi Don,

Your query wil not execute; It is missing an "AND".

When that is corrected, you will not get a cartesian product result set; you will get the ~250.000 rows in mdc.

But the query will most likely produce some huge sorts, which cause the temp space growth and response time. Check the execution plan with EXPLAIN and tune afterwards (e.g. add appropriate indexes).

BTW : Since you are running 9i, you should get rid of the (+) operator and use the regular ANSI 99 Syntax :

SELECT mdc.id

   FROM part LEFT OUTER JOIN (SELECT id, code, sos FROM tableB) mdc

        ON part.code = mdc.code
      AND part.sos = mdc.sos

• Kenneth Koenraadt