JOINs against a subquery

I have a pretty complicate query that produces a sales summary,
something similar to this:

customer_id sales_amount
=========== ============
1 99.95
2 125.50
3 1.25

The query is complicate because it reads the table where individual
sales are stored and it needs to filter out some lines according to
variable criteria.

I need to print this data, together with the customer details (full
name, city, etc.). So I join this resultset with the customers table:

customer_id full_name city
=========== ========= ======
1 Joe M. Paris
3 Jack X. London

(Yep, there ain't foreign keys; I'm reading from a third-party database).

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. Am I making a wrong assumption somewhere?


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

On Nov 25, 7:02 am, "Álvaro G. Vicario"
wrote:
> I have a pretty complicate query that produces a sales summary,
> something similar to this:
>
> customer_id sales_amount
> =========== ============
>            1        99.95
>            2       125.50
>            3         1.25
>
> The query is complicate because it reads the table where individual
> sales are stored and it needs to filter out some lines according to
> variable criteria.
>
> I need to print this data, together with the customer details (full
> name, city, etc.). So I join this resultset with the customers table:
>
> customer_id full_name city
> =========== ========= ======
>            1 Joe M.    Paris
>            3 Jack X.   London
>
> (Yep, there ain't foreign keys; I'm reading from a third-party database).
>
> 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. Am I making a wrong assumption somewhere?
>
> --
> --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain

Just a guess as the Oracle version was not specified and the DBMS
Xplans were not posted, but from your description, it sounds like
Oracle is taking the CUSTOMERS table and attempting to drive into the
inline view that you aliased as SA. Essentially, this means that for
each row in the CUSTOMERS table, the full view is resolved. This
seems to happen on more frequently Oracle 10g (R2) than it did in
older versions. You are able to control this behavior through the use
of hints. With a LEADING hint in place to control the driving data
set, the query would look like this:
SELECT /*+ LEADING(SA) */
sa.customer_id, sa.sales_amount, cu.full_name, cu.city
FROM (
// Complicate inner query, returns only a few rows
) sa
LEFT OUTER JOIN customers cu ON sa.customer_id=cu.customer_id;

The Oracle specific syntax, rather than ANSI syntax, looks like this:
SELECT /*+ LEADING(SA) */
sa.customer_id, sa.sales_amount, cu.full_name, cu.city
FROM (
// Complicate inner query, returns only a few rows
) sa,
customers cu
WHERE
sa.customer_id=cu.customer_id(+);

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.