Re: JOINs against a subquery

"Álvaro G. Vicario" <alvaroNOSPAMTHA..._at_demogracia.com> wrote:
> I have a pretty complicate query that produces a sales
> summary, ...

<snip>
> My worry is that I must be missing something basic about
> Oracle. An INNER JOIN works as expected and is almost as
> fast as the main query:
>
> SELECT sa.customer_id, sa.sales_amount, cu.full_name, cu.city
> FROM (
>         // Complicate inner query, returns only a few rows
> ) sa
> INNER JOIN customers cu ON sa.customer_id=cu.customer_id
>
> customer_id sales_amount full_name city
> =========== ============ ========= ======
>            1        99.95 Joe M.    Paris
>            3         1.25 Jack X.   London
>
> However, a LEFT JOIN takes years to run. The explain plan
> is completely different and has a cost that's more than
> ten times larger. So I cannot get this:
>
> customer_id sales_amount full_name city
> =========== ============ ========= ======
>            1        99.95 Joe M.    Paris
>            2       125.50 NULL      NULL
>            3         1.25 Jack X.   London
>
> I'd say the outer query would run after the inner query
> is completed but it doesn't seem to be the case.

Have you tried...

  select sa.customer_id,

         sa.sales_amount,
         (select cu.full_name from customers cu
           where cu.customer_id = sa.customer_id) full_name,
         (select cu.city from customers cu
           where cu.customer_id = sa.customer_id) city

Although this is notionally slower, if the complex query is only returning a few rows, then it may work out quicker.

P.S. I'd be inclined to post the complex query.

--
Peter