 | |
JOINs against a subquery
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: JOINs against a subquery -
11-26-2008
, 07:59 PM
|
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. |
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
#12
| |||
| |||
|
|
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. |
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
#13
| |||
| |||
|
|
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. |
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
#14
| |||
| |||
|
|
"Álvaro G. Vicario" <alvaroNOSPAMTHA... (AT) demogracia (DOT) 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 from (...complex...) sa Although this is notionally slower, if the complex query is only returning a few rows, then it may work out quicker. |
in another query and it's definitively faster.
The problem was that I had a base misconception about how joins and
subqueries work. Now I understand that the Oracle optimizer can fully
rewrite the query so the order and position in which I write the query
elements do not imply that they'll be executing in that same order. More
specifically, parenthesis do not mean "calculate this first" like in maths.
With this in mind, I'm already getting better performance in several
places (though my SQL looks weirder...)
--
-- 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
--
#15
| |||
| |||
|
|
"Álvaro G. Vicario" <alvaroNOSPAMTHA... (AT) demogracia (DOT) 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 from (...complex...) sa Although this is notionally slower, if the complex query is only returning a few rows, then it may work out quicker. |
in another query and it's definitively faster.
The problem was that I had a base misconception about how joins and
subqueries work. Now I understand that the Oracle optimizer can fully
rewrite the query so the order and position in which I write the query
elements do not imply that they'll be executing in that same order. More
specifically, parenthesis do not mean "calculate this first" like in maths.
With this in mind, I'm already getting better performance in several
places (though my SQL looks weirder...)
--
-- 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
--
#16
| |||
| |||
|
|
"Álvaro G. Vicario" <alvaroNOSPAMTHA... (AT) demogracia (DOT) 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 from (...complex...) sa Although this is notionally slower, if the complex query is only returning a few rows, then it may work out quicker. |
in another query and it's definitively faster.
The problem was that I had a base misconception about how joins and
subqueries work. Now I understand that the Oracle optimizer can fully
rewrite the query so the order and position in which I write the query
elements do not imply that they'll be executing in that same order. More
specifically, parenthesis do not mean "calculate this first" like in maths.
With this in mind, I'm already getting better performance in several
places (though my SQL looks weirder...)
--
-- 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
--
#17
| |||
| |||
|
|
"Álvaro G. Vicario" <alvaroNOSPAMTHA... (AT) demogracia (DOT) 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 from (...complex...) sa Although this is notionally slower, if the complex query is only returning a few rows, then it may work out quicker. |
in another query and it's definitively faster.
The problem was that I had a base misconception about how joins and
subqueries work. Now I understand that the Oracle optimizer can fully
rewrite the query so the order and position in which I write the query
elements do not imply that they'll be executing in that same order. More
specifically, parenthesis do not mean "calculate this first" like in maths.
With this in mind, I'm already getting better performance in several
places (though my SQL looks weirder...)
--
-- 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
--
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.