Re: JOINs against a subquery

On Nov 25, 7:02 am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA..._at_demogracia.com> 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. Received on Tue Nov 25 2008 - 06:31:05 CST