Re: help with select statement
From: joel garry <joel-garry_at_home.com>
Date: Mon, 19 Jan 2009 11:16:14 -0800 (PST)
Message-ID: <21d71397-26de-4938-a5f1-a44a8f54c085_at_g39g2000pri.googlegroups.com>
On Jan 18, 10:28 pm, wibni <Boettger.Andr..._at_gmail.com> wrote:
> Brilliant! It works! Thanks Gerard!
>
> I also got it working in an analytic function but Gerard's statement
> is actually quicker then my function. I always thought a function is
> the fastest way.
> Never mind! Thanks again all!
>
> select distinct name, swcode
> from
> (select name, swcode,
> count(case when relkeyword.swcode = 173 then 1 end) over (partition by
> cscontact.name) as cnt173
> from cscontact,relkeyword
> where cscontact.pubindex = relkeyword.relpubindex(+))
> where cnt173 = 0
> order by name
Date: Mon, 19 Jan 2009 11:16:14 -0800 (PST)
Message-ID: <21d71397-26de-4938-a5f1-a44a8f54c085_at_g39g2000pri.googlegroups.com>
On Jan 18, 10:28 pm, wibni <Boettger.Andr..._at_gmail.com> wrote:
> Brilliant! It works! Thanks Gerard!
>
> I also got it working in an analytic function but Gerard's statement
> is actually quicker then my function. I always thought a function is
> the fastest way.
> Never mind! Thanks again all!
>
> select distinct name, swcode
> from
> (select name, swcode,
> count(case when relkeyword.swcode = 173 then 1 end) over (partition by
> cscontact.name) as cnt173
> from cscontact,relkeyword
> where cscontact.pubindex = relkeyword.relpubindex(+))
> where cnt173 = 0
> order by name
Which is faster "depends." As a general rule, distinct often forces a sort and winds up being slow, especially if the sort spills to temp (check the docs or EM about one-pass and multipass). As a general rule, analytics often allow not switching contexts, and so are better than PL, but whether it is better than built-in functions depends.
As a general rule, I would not expect a distinct to be faster than a properly written analytic. But the optimizer can sometimes do very sophisticated and surprising things, or sometimes the data just works better with a particular approach. Did you have an outer join in the analytic?
jg
-- _at_home.com is bogus. http://www.techcrunch.com/2009/01/14/facebook-blows-a-whopper-of-an-opportunity/Received on Mon Jan 19 2009 - 13:16:14 CST