JOINs against a subquery

#1

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
--

#2

On Nov 25, 7:02*am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:

Quote:

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.

#3

On Nov 25, 7:02*am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:

Quote:

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.

#4

On Nov 25, 7:02*am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:

Quote:

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.

#5

On Nov 25, 7:02*am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:

Quote:

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.

#6

Charles Hooper escribió:

Quote:

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.

Sorry, I forgot to mention it's 10g R1. I didn't post actual data
because it gets quite complicated and I just wanted some general advise.
(Furthermore, I generate explain plans with SQL Developer and I don't
have the faintest idea of how to export it into a readable format...)

I see my misconception was believing that the subquery was independent
from the external query. No matter how the code looks, the Oracle
optimizer rewrites it all trying to find the optimal execution plan.

I'll have a look at the documentation about optimizer hints.

Thank you very much.

--
-- 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
--

#7

Charles Hooper escribió:

Quote:

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.

Sorry, I forgot to mention it's 10g R1. I didn't post actual data
because it gets quite complicated and I just wanted some general advise.
(Furthermore, I generate explain plans with SQL Developer and I don't
have the faintest idea of how to export it into a readable format...)

I see my misconception was believing that the subquery was independent
from the external query. No matter how the code looks, the Oracle
optimizer rewrites it all trying to find the optimal execution plan.

I'll have a look at the documentation about optimizer hints.

Thank you very much.

--
-- 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
--

#8

Charles Hooper escribió:

Quote:

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.

Sorry, I forgot to mention it's 10g R1. I didn't post actual data
because it gets quite complicated and I just wanted some general advise.
(Furthermore, I generate explain plans with SQL Developer and I don't
have the faintest idea of how to export it into a readable format...)

I see my misconception was believing that the subquery was independent
from the external query. No matter how the code looks, the Oracle
optimizer rewrites it all trying to find the optimal execution plan.

I'll have a look at the documentation about optimizer hints.

Thank you very much.

--
-- 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
--

#9

Charles Hooper escribió:

Quote:

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.

Sorry, I forgot to mention it's 10g R1. I didn't post actual data
because it gets quite complicated and I just wanted some general advise.
(Furthermore, I generate explain plans with SQL Developer and I don't
have the faintest idea of how to export it into a readable format...)

I see my misconception was believing that the subquery was independent
from the external query. No matter how the code looks, the Oracle
optimizer rewrites it all trying to find the optimal execution plan.

I'll have a look at the documentation about optimizer hints.

Thank you very much.

--
-- 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
--

#10

"Álvaro G. Vicario" <alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:

Quote:

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
from (...complex...) sa

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