Re: getting in a little over my head

      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_at_roughsea.com>

> To:<JSweetser_at_icat.com>

> Cc: "Oracle L"<oracle-l_at_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