JOINs against a subquery

From: Álvaro G. Vicario <alvaroNOSPAMTHANKS_at_demogracia.com>
Date: Tue, 25 Nov 2008 13:02:32 +0100
Message-ID: <gggphb$1n1$1@huron.algomas.org>


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?

--

Received on Tue Nov 25 2008 - 06:02:32 CST

Original text of this message