Re: JOINs against a subquery
From: Álvaro G. Vicario <alvaroNOSPAMTHANKS_at_demogracia.com>
Date: Thu, 27 Nov 2008 10:08:19 +0100
Message-ID: <gglo1u$7qm$1@huron.algomas.org>
>> I have a pretty complicate query that produces a sales
>> summary, ...
>> 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.
Date: Thu, 27 Nov 2008 10:08:19 +0100
Message-ID: <gglo1u$7qm$1@huron.algomas.org>
Peter Nilsson escribió:
> "Álvaro G. Vicario" <alvaroNOSPAMTHA..._at_demogracia.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.
I haven't fined-tuned this query yet, but I've used a similar approach 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 --Received on Thu Nov 27 2008 - 03:08:19 CST