Message-ID: <4E69CF1A.9000708@roughsea.com>
Date: Fri, 09 Sep 2011 10:32:26 +0200
From: Stephane Faroult <sfaroult@roughsea.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.20) Gecko/20110804 SUSE/3.1.12 Thunderbird/3.1.12
MIME-Version: 1.0
To: jonathan@jlcomp.demon.co.uk
CC: Oracle L <oracle-l@freelists.org>
Subject: Re: getting in a little over my head
References: <D18D6513433DF04394041EA42B53E91C57884719@ICATEXCH2.ICAT.com> <4E69BFBE.6000803@roughsea.com> <840CC2221F934911BC8685C1400C7A7C@Primary>
In-Reply-To: <840CC2221F934911BC8685C1400C7A7C@Primary>
Content-type: text/plain
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: sfaroult@roughsea.com

Jonathan,
      Or one or two DISTINCT, you are right. I always tend to put UNION 
ALL by default when I have different constants in my select list, but 
nothing says that objectid is unique in either quote for accountid, or 
in pol_policy for (accountid, logid).


-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


On 09/09/2011 10:17 AM, Jonathan Lewis wrote:
>
> Stephane,
>
> It depends on the uniqueness constraints, of course, but I think (based on the
> information we have so far)
> that the "UNION ALL" should be a "UNION".
>
> FROM (select objectid objid, 'Quote' linktype
>         FROM quote
>         WHERE accountid = 128847
>         union all
>         select objectid objid, 'Policy' linktype
>         FROM pol_policy
>         WHERE accountid = 128847
>           AND logid = 1) x
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
>
> ----- Original Message -----
> From: "Stephane Faroult"<sfaroult@roughsea.com>
> To:<JSweetser@icat.com>
> Cc: "Oracle L"<oracle-l@freelists.org>
> Sent: Friday, September 09, 2011 8:26 AM
> Subject: Re: getting in a little over my head
>
>
> Joe,
>       I have never been a great fan of execution plans but I have always
> hated multiple subqueries that kind of "sequentialize" everything. For
> me, everything depends in your query about the selectivity of accountid
> in tables quote and pol_policy, and my gut instinct would make me
> rewrite your query like this:
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


--
http://www.freelists.org/webpage/oracle-l